Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104

    Unanswered: declaring primary/foreign keys in sql

    Hi

    If i have two foreign keys in a table which make up my primary key, do i actually write in the SQL that they are the primary keys?

    I know you can't declare more than one primary key in a table, eg

    CREATE TABLE FACILITIES(
    venue_id INTEGER PRIMARY KEY REFERENCES VENUE NOT NULL,
    facility_id INTEGER PRIMARY KEY REFERENCES FACILITY_TYPE NOT NULL
    )

    ....so do i just reference them as foreign keys?

    eg

    CREATE TABLE FACILITIES(
    venue_id INTEGER REFERENCES VENUE NOT NULL,
    facility_id INTEGER REFERENCES FACILITY_TYPE NOT NULL
    )



    thanks in advance!
    Last edited by mattock; 08-09-06 at 07:42.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    create table facilities
    ( venue_id    integer not null  references venue 
    , facility_id integer not null  references facility_type 
    , primary key (venue_id, facility_id)
    )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104
    Quote Originally Posted by r937
    Code:
    create table facilities
    ( venue_id    integer not null  references venue 
    , facility_id integer not null  references facility_type 
    , primary key (venue_id, facility_id)
    )

    Ahh right, i see, so you have to define the primary key seperatly! - i have a 450+ page book on database design and it doesn't mention how to do that ....thanks again r937

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oh, it's gotta be in there somewhere!!

    i cannot imagine a database book that overlooks a composite primary key

    tell me, does it discuss how to implement a many-to-many relationship? what does the table in the middle look like?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104
    It tells you that you can create a primary key using two foreign keys (composite) and shows it in the ERD, but i couldn't seem to find an example on how to actually create it!

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    too bad, eh -- well, now you know

    and of course your database system's documentation would cover it in its CREATE TABLE syntax
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •