Foreign Keys with Composite Key

composite-typesdatabase-designforeign keyrelational-theory

Imagine I have a table with the following dependency:

(Name, BoughtFrom, TimeBought) –> Cost

Where the three attributes in bold form a composite primary key for the table. Now then, I want to link (form a relationship) from another table to this one. How do I do that? If I had an ID column I'd know what to do, but I've never come across a scenario like this.

Would I have to add all three columns (Name, BoughtFrom, TimeBought) to the other table? Or is there another way?

Best Answer

Yes, you'd add all three columns. Assuming they have the same names in both tables, you'd use something like

foreign key (Name, BoughtFrom, TimeBought) 
  references the_other_table_name (Name, BoughtFrom, TimeBought)

If you decide to use a surrogate ID number, you'll still need a unique constraint on {Name, BoughtFrom, TimeBought}. You can do that with something along these lines.

create table your_table (
  your_id_number integer primary key,
  Name ... ,
  BoughtFrom ... ,
  TimeBought ... ,
  unique (Name, BoughtFrom, TimeBought)
);

The surrogate key doesn't have to be an ID number. It doesn't have to be any kind of number. But an automatically incrementing ID number is the most common.

Surrogate means takes the place of. A surrogate key, like an ID number, takes the place of a natural key. Kind of like a surrogate mother takes the place of a natural mother.

Related Solutions

Simple scenario needs to go from Unnormalized Data to 3NF – Review Please

Yes, it's in 1NF.

You can't side-step the often hard work of determining all the candidate keys by hanging a number off the end of the table and saying, "There. I've got a primary key." One natural candidate key for this table is {Name, Bought from, Date bought}. Consider using "Time bought" instead of "Date bought".

Your definition of 2NF is wrong. Instead of

Second Normal Form: A relation that is in First Normal Form and every non-primary-key attribute is fully functionally dependent on the primary key.

you need something more like this.

Second Normal Form: a relation that is in First Normal Form, and every non-prime attribute is fully functionally dependent on every candidate key.

The term non-prime attribute doesn't mean quite what non-primary-key attribute means.

Your definition of 3NF is wrong, and it's wrong for the same reasons as your definition for 2NF was wrong.

Instead of this

Third Normal Form: A relation that is in First and Second Normal Form and in which no non-primary-key attribute is transitively dependent on the primary key.

you need something closer to this.

Third Normal Form: A relation that is in Second Normal Form and in which every non-prime attribute is nontransitively dependent on every candidate key. (There isn't a really good way to express all those negative in one sentence.)

Composite primary key from multiple tables / multiple foreign keys

Short version:

CREATE TABLE Relation
( stu_id INT NOT NULL REFERENCES Student,
  par_id INT NOT NULL REFERENCES Parent,
  PRIMARY KEY (stu_id, par_id)
) ;

Long version:

Why use short forms for names, like stu_id and par_id? Why not student_id? Saving typing 3-4 characters? How will you differentiate between parent_id and parameter_id? Or school_id and schoolmaster_id?
The name "Relation" is not very descriptive for a relationship. (Note also that in relational model terminology, "relation" has a meaning very close to "table".) I couldn't come with a good name though, so we could use "Guardian" or "Student_Parent" (this combination is often used in intersection tables)
The short version above is just an example. While it is working, it uses a lot of shortcuts, like the inline references. It's far better in my opinion, to name all constraints and declare all (primary, unique, foreign key and check) constraints after the column declarations, like in the long version below.
It's also good to choose some naming conventions anduse them consistently in all the tables, e.g. Tablename_PK for the primary keys, ReferencedTable_referencingTable_FK for the foreign keys, Something_UQ for the unique constraints, etc.

CREATE TABLE Guardian
(                                        -- columns
  student_id INT NOT NULL, 
  parent_id INT NOT NULL,
                                         -- constraints
  CONSTRAINT Guardian_PK                 -- the name of the PK constraint
    PRIMARY KEY (student_id, parent_id),

  CONSTRAINT Student_Guardian_FK         -- the name of the FK constraint
    FOREIGN KEY (student_id) 
    REFERENCES Student (student_id)
      ON UPDATE CASCADE                  -- the actions of the FK
      ON DELETE RESTRICT,
  CONSTRAINT Parent_Guardian_FK          -- second FK
    FOREIGN KEY (parent_id) 
    REFERENCES Parent (parent_id)
      ON UPDATE CASCADE
      ON DELETE RESTRICT
) ;

Best Answer

Related Solutions

Simple scenario needs to go from Unnormalized Data to 3NF – Review Please

Composite primary key from multiple tables / multiple foreign keys

Related Question