Re: Primary Keys and Foreign Keys

>> If I define a composite Primary key in Lives (person_id, house_id)
is there any need to set a foreign key in the other tables? If a foreign key needs to be set, it is correct to define house_id in Houses and person_id in Person as a Primary Key AND foreign key to reference the composite primary key in Lives? <<

That is not possible. A key exists in one and only one table, not split across the schema. Why not post real DDL to make things clearer?

CREATE TABLE Persons
(person_id INTEGER NOT NULL PRIMARY KEY,  name VARCHAR(30) NOT NULL,
 dob DATE NOT NULL);

CREATE TABLE Houses
(house_id INTEGER NOT NULL PRIMARY KEY
 address VARCHAR(35) NOTB NULL,
 zipcode CHAR(5) NOT NULL);

CREATE TABLE Occupancy
(person_id INTEGER NOT NULL

     REFERENCES Persons(person_id),
 house_id INTEGER NOT NULL

     REFERENCES Houses(house_id),
 from_date DATE DEFAULT CURRENT_DATE NOT NULL,  to_date DATE, -- null means still there  CHECK(from_date <= to_date),
 ...,
 PRIMARY KEY (person_id, house_id, from_date));

There is no rule that says a column cannot be in the primary key of its own table while referencing another table. Received on Mon Apr 14 2003 - 20:43:49 CEST