declaring primary/foreign keys in sql

mattock · #1 (**permalink**) 08-09-06, 05:52

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!

r937 · #2 (**permalink**) 08-09-06, 07:27

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)
)

mattock · #3 (**permalink**) 08-09-06, 07:36

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

r937 · #4 (**permalink**) 08-09-06, 07:41

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?

mattock · #5 (**permalink**) 08-09-06, 07:48

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!

r937 · #6 (**permalink**) 08-09-06, 07:56

too bad, eh -- well, now you know

and of course your database system's documentation would cover it in its CREATE TABLE syntax

Internet Services

Web Development

Digital Marketing

Consumer Tech