How I can give foreign key on multiple columns

Mohan Chandra

10y927SQL

Hi,I need help,I am just learning this time
I have a table Invoice
which has fields
InvoiceNo
Invoice_amount
Invoice_Date
Exchange_Rate

and second table is Receipt
which has fields
ReceiptNo
ReceiptAmount
Receipt_Date
InvoiceNo
InvoiceDate
Exchange_Rate

Now What I need to do, here suppose i created a Invoice table

create table Invoice ( InvoiceNo varchar(15) not null, InvoiceAmount money not null, invoiceDate date not null, ExchangeRate Money not null, constraint pk_invoice primary key(InvoiceNo), constraint uk_inv unique (InvoiceNo, InvoiceAmount) )

I am just trying to create two tables Invoice and Receipt
I have created Invoice table but On receipt table I need references on
InvoiceNo, InvoiceDate so how I can give references when we need to give references on multiple columns.

please help me
i need help for how I can give foreign key references on multiple column

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke10y ago
Accepted answer
On whatever columns you want to create a foreign key, the primary or unique key must exist on referenced table.
I am almost sure that your InvoiceNo is going to be unique. So your Invoice No + Invoice Date is also going to be unique, you can create either primary key or create a unique key on that combination.
Hope it helps. Any questions, please ask.
+1
Mohan Chandra10y ago
thanks i got it now,
one question more please
can we give foreign key on a column which is unique key of another table?
means we can give foreign key of a table which is primary key column of another.
but only one primary key we can give in a table so can we give foreign key on a column which is unique key of another table(base table)
0
Mohan Chandra10y ago
ya i tried but error is shown there is no primary key or candidate key.
i have confusion that which column i should take for primary key......
if i give primary key on multiple column then combination of column should not be duplicate.
like i give primary key on invoiceNo and Invoice_date then there is possibility that
INv01 07 july 2016
INv02 07 july 2016
but again
INv01 8 july 2016
in that case what i need to do
0
Nitin Sontakke10y ago
Hello,
Did you manage to see and try my answer?
0
Mohan Chandra10y ago
If I give directly foreign key then there are no primary key or candidate key error shown...

Gagan sir we have to create multiple table means more than two table for that as you share link,,,,,,,,
0
Nitin Sontakke10y ago
At the end of the script just add:
constraint [FK_Receipt_InvoiceNo_InvoiceDate_Invoice_InvoiceNo_InvoiceDate] foreign key ([InvoiceNo], [InvoiceDate]) references Invoice([InvoiceNo], [InvoiceDate])
+1
Gagan Gautam10y ago
check this link
http://stackoverflow.com/questions/15547276/it-is-possible-to-reference-one-column-as-multiple-foreign-keys
+1