March 22, 2013 at 3:48 am
Hi,
I have a table Gv_voucher which has data like(VoucherId, VoucherNo, TranactionID)
1, VVB0001, TRN001
2,VVB0002, TRN001
3,VVB003,TRN002
4,VVB0004, TRN002
I have created another table GV_ReceivedOffice in which I required these column VoucherNo and TransactionID
I have created a unique constraint on column VoucherNO in GV_Vocuher table so that I can use it in my another table Gv_received.
But how to use column TransactionID of Gv_Voucher table in GV_ReceivedOffice. Is it so that without creating constraint we cant reference a column to another table's column?
Please suggest.
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 22, 2013 at 6:46 am
Yes. A FOREIGN KEY constraint has to be linked to either a PRIMARY KEY constraint or a UNIQUE constraint in another table.
If the column(s) are not part of PRIMARY/UNIQUE constraint, then duplication is possible in the PRIMARY table which will create ambiguity for the FOREIGN KEY constraint.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 22, 2013 at 7:00 am
Duplicate post
Please post further replies here
http://www.sqlservercentral.com/Forums/Topic1434157-391-1.aspx
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 22, 2013 at 7:15 am
Kingston Dhasian (3/22/2013)
Yes. A FOREIGN KEY constraint has to be linked to either a PRIMARY KEY constraint or a UNIQUE constraint in another table.If the column(s) are not part of PRIMARY/UNIQUE constraint, then duplication is possible in the PRIMARY table which will create ambiguity for the FOREIGN KEY constraint.
Yes Unique constrant is there on VoucherNo, but how can I createe constraint on TransactionId column as it will be same for multiple VoucherNo
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 22, 2013 at 8:05 am
kapil_kk (3/22/2013)
Kingston Dhasian (3/22/2013)
Yes. A FOREIGN KEY constraint has to be linked to either a PRIMARY KEY constraint or a UNIQUE constraint in another table.If the column(s) are not part of PRIMARY/UNIQUE constraint, then duplication is possible in the PRIMARY table which will create ambiguity for the FOREIGN KEY constraint.
Yes Unique constrant is there on VoucherNo, but how can I createe constraint on TransactionId column as it will be same for multiple VoucherNo
You can create a FOREIGN KEY constraint only when there is a One -> Many relationship between the PRIMARY and the FOREIGN KEY table.
In your case there is a Many -> One/Many relationship which makes the implementation illogical.
You should probably try creating a composite FOREIGN KEY constraint with 2 columns together( VoucherNo and TransactionId )
For that again, you will need a composite UNIQUE constraint on these 2 columns in your PRIMARY table.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 22, 2013 at 12:24 pm
Kingston Dhasian (3/22/2013)
kapil_kk (3/22/2013)
Kingston Dhasian (3/22/2013)
Yes. A FOREIGN KEY constraint has to be linked to either a PRIMARY KEY constraint or a UNIQUE constraint in another table.If the column(s) are not part of PRIMARY/UNIQUE constraint, then duplication is possible in the PRIMARY table which will create ambiguity for the FOREIGN KEY constraint.
Yes Unique constrant is there on VoucherNo, but how can I createe constraint on TransactionId column as it will be same for multiple VoucherNo
You can create a FOREIGN KEY constraint only when there is a One -> Many relationship between the PRIMARY and the FOREIGN KEY table.
In your case there is a Many -> One/Many relationship which makes the implementation illogical.
You should probably try creating a composite FOREIGN KEY constraint with 2 columns together( VoucherNo and TransactionId )
For that again, you will need a composite UNIQUE constraint on these 2 columns in your PRIMARY table.
Ok thanks, will try with this....
But I also a suggestion that I can create a trigger for insertion of TransactionID while doing insertion in that table
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 22, 2013 at 12:34 pm
You can implement the same thing using triggers as well but constraints are better than triggers for such requirements.
Moreover, I think what you need is a composite FOREIGN KEY constraint
Check the link below for some information on FOREIGN KEY constraints
http://msdn.microsoft.com/en-IN/library/ms175464%28v=sql.105%29.aspx
Edit:Added a link
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply