Foreign Key Issues | MySmartChannels
Below is my response to Go Blue's blog on composite primary and foreign key creation issues she has been experiencing while trying to create her project's E-R diagram. My group and I had pretty much the same problem to begin with, but have since figured it out with the help of Bud.
To create a composite primary key you can not simply write primary key after each entity you wish to designate as part of the primary key. Instead, you have to go about it one of two ways:
1) Specify no primary keys, create the table, and then use the function "alter table, add primary key" to create the primary keys. For example, your creation statements would look something like this:
Create Table Blog (Author varchar(20), Title varchar(20));
Alter Table Blog
Add Primary Key (Author, Title);
(look at pg 150 of SQL book for more help)
OR
2) Write all the entities of the table following the create command, then write primary key followed by parentheses with all of the entities that make up the primary key. For example:
Create Table Blog (Author varchar(20), Title varchar(20), Category varchar(20), Primary Key (Author, Title) );
(look at pg 41 for more help)
Next, in order to create foreign keys that refer to composite primary keys of a table, you first create the new table and then use the "alter table, add forgein key" function once and put all the foreign keys into the parentheses in the SAME order as you created the primary keys of the table you are referring to.
Here is an example:
Create Table .....,
Alter Table Team
Add Foreign Key (Author, Title) References Blog
*Notice the composite primary key is added TOGETHER as a foreign key and in the SAME order.
Hope this helps.