how do you add a foreing key to a table that has two primary keys

Question

1.00/5 (1 vote)

See more:

use LifeSpireDB;

create table Customer(
Customer_ID int,
Customer_Name varchar(50),
Customer_Address varchar(50),
Customer_Phone varchar(50),
primary key (customer_ID));

create table Orders(
Order_Num int,
Order_Date varchar(10),
Order_Status varchar(50),
Customer_ID int,
primary key(Order_Num),
constraint fk_PerCostumer foreign key(Customer_ID) references Customer(Customer_ID));

create table ItemsInOrder(
Order_Num int,
Item_ID varchar(15)
primary key (Order_Num, Item_ID),
constraint fk_perItemsOder foreign key(Order_Num) references Orders(Order_Num));

create table Item(
Item_UID int,
Item_ID varchar(15),
Item_Description varchar(300)
primary key (Item_UID),
constraint fk_PerItem foreign key (Item_ID) references ItemsInOrder(Item_ID));

My table Item has a foreign key which is a primary key in table ItemsInOrder, the problem is the ItemsInOrder has two primary keys.

Can anyone help me please???

Posted 26-Feb-14 11:07am

Alexander24

Add a Solution

Comments

Sergey Alexandrovich Kryukov 26-Feb-14 19:32pm

If you managed to create two primary keys, your skills are so formidable that you will easily add one foreign key without any help. :-)
—SA

Alexander24 1-Mar-14 13:40pm

Yeah I solved the problem by following some tutorials online. Thank you though

Sergey Alexandrovich Kryukov 1-Mar-14 13:42pm

Congratulations! This is the best you could do. Thank you for reporting back.
—SA

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Andrius Leonavicius · Answer 1 · 2014-02-26T11:21:00

Hi,

First of all, you can have only one primary key. You can have multiple columns in your primary key. Such key is called a compound or composite primary key.

According to your example:

SQL

create table Item(
Item_UID int,
Item_ID varchar(15),
Item_Description varchar(300)
primary key (Item_UID),
constraint fk_PerItem foreign key (Item_ID) references ItemsInOrder(Order_Num, Item_ID));

This is important: You need to have a Order_Num column in your Item table (I haven't added it into this example). Otherwise, you just can't create a foreign key relationship!

---------------------------------------------------------------------------------------
My thoughts about your data model:
1. Why do you need a composite primary key in ItemsInOrder table? I would say that you could add ItemsInOrder_ID column and make it as simple primary key.
2. Relationship between Item and ItemsInOrder tables should be one-to-many.
3. Relationship between Orders and ItemsInOrder tables should be one-to-many.