How I can give foreign key on multiple columns

Question

HiI need helpI 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

Nitin Sontakke · 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.

Nitin Sontakke · Answer

At the end of the script just add: constraint [FK_Receipt_InvoiceNo_InvoiceDate_Invoice_InvoiceNo_InvoiceDate] foreign key ([InvoiceNo] [InvoiceDate]) references Invoice([InvoiceNo] [InvoiceDate])

Gagan Gautam · Answer

check this link http://stackoverflow.com/questions/15547276/it-is-possible-to-reference-one-column-as-multiple-foreign-keys

Mohan Chandra · Answer

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)

Mohan Chandra · Answer

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

Nitin Sontakke · Answer

Hello Did you manage to see and try my answer?

Mohan Chandra · Answer

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