Colulmn Reference

Question

Post reply

Colulmn Reference

kapil_kk

SSC-Insane

Points: 21316
More actions
April 25, 2013 at 11:50 pm

#274642

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/

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

sql-programmers SSC-Addicted Points: 484 More actions · Answer 1

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.

SQL Sever Performance tuning

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 2

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

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

kapil_kk SSC-Insane Points: 21316 More actions · Answer 3

Kingston Dhasian (4/26/2013)
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

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/

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 4

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)

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

kapil_kk SSC-Insane Points: 21316 More actions · Answer 5

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/

kapil_kk SSC-Insane Points: 21316 More actions · Answer 6

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/