April 25, 2013 at 11:50 pm
CREATE TABLE [dbo].[GV_ReceivedOffice](
[ReceivedOfficeID] [int] IDENTITY(1,1) NOT NULL,
[VoucherNo] [varchar](20) NULL,
[ReceivedDate] [datetime] NULL,
[ReceivedBy] [int] NULL,
[TransactionID] [varchar](20) NOT NULL,
PRIMARY KEY CLUSTERED
(
[ReceivedOfficeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
I am trying to add a constraint to a column TransactionID so that it can references column 'TransactionID' of table GV_vocuher
ALTER TABLE GV_ReceivedOffice
ADD CONSTRAINT FK_ReceivedOffice_VocuherTransactionID FOREIGN KEY(TransactionID) REFERENCES GV_Voucher(TransactionID)
But I am getting this error:
Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table 'GV_Voucher' that match the referencing column list in the foreign key 'FK_ReceivedOffice_VocuherTransactionID'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 26, 2013 at 12:50 am
Hi,
When you create a foreign key, it should reference either primary key or Unique key.
In table GV_Voucher, please make sure TransactionID as Primary Key or Unique key.
April 26, 2013 at 2:33 am
In your table GV_Voucher, the column TransactionID has not been defined as a UNIQUE KEY
You will have to make TransactionID a UNIQUE KEY in GV_Voucher to create this FOREIGN KEY constraint
But, as I mentioned in your other thread, if the column TransactionID is indeed unique, then you need not add the column in your GV_ReceivedOffice table at all.
http://www.sqlservercentral.com/Forums/Topic1446803-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/
April 26, 2013 at 3:08 am
Kingston Dhasian (4/26/2013)
In your table GV_Voucher, the column TransactionID has not been defined as a UNIQUE KEYYou will have to make TransactionID a UNIQUE KEY in GV_Voucher to create this FOREIGN KEY constraint
But, as I mentioned in your other thread, if the column TransactionID is indeed unique, then you need not add the column in your GV_ReceivedOffice table at all.
http://www.sqlservercentral.com/Forums/Topic1446803-391-1.aspx
TransactionID is not unique..
But as VoucherNo is unique so I created a unique constraint combining these two columns (VoucherNO, TransactionID)
but still am getting that error
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 26, 2013 at 3:46 am
Try creating a UNIQUE KEY constraint with both the columns on GV_Voucher table.
Your FOREIGN KEY constraint in GV_ReceivedOffice table should again be based on these 2 columns.
ALTER TABLE GV_ReceivedOffice ADD CONSTRAINT FK_ReceivedOffice_Voucher_VoucherNo_TransactionID
FOREIGN KEY (VoucherNo,TransactionID) REFERENCES GV_Voucher (VoucherNo,TransactionID)
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 26, 2013 at 5:16 am
Kingston Dhasian (4/26/2013)
Try creating a UNIQUE KEY constraint with both the columns on GV_Voucher table.Your FOREIGN KEY constraint in GV_ReceivedOffice table should again be based on these 2 columns.
ALTER TABLE GV_ReceivedOffice ADD CONSTRAINT FK_ReceivedOffice_Voucher_VoucherNo_TransactionID
FOREIGN KEY (VoucherNo,TransactionID) REFERENCES GV_Voucher (VoucherNo,TransactionID)
Thanks,
it worked
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 26, 2013 at 5:17 am
Kingston Dhasian (4/26/2013)
Try creating a UNIQUE KEY constraint with both the columns on GV_Voucher table.Your FOREIGN KEY constraint in GV_ReceivedOffice table should again be based on these 2 columns.
ALTER TABLE GV_ReceivedOffice ADD CONSTRAINT FK_ReceivedOffice_Voucher_VoucherNo_TransactionID
FOREIGN KEY (VoucherNo,TransactionID) REFERENCES GV_Voucher (VoucherNo,TransactionID)
I also tried in this manner but was getting error:
ALTER TABLE GV_ReceivedOffice
ADD CONSTRAINT FK_ReceivedOffice_VocuherTransactionID1
FOREIGN KEY(TransactionID, VoucherNO)
REFERENCES GV_Voucher(TransactionID, VoucherNo)
Yours one get executed successfully... dont know why.. just changing the order created the references?
_______________________________________________________________
To get quick answer follow this link:
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy