Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Colulmn Reference Expand / Collapse
Author
Message
Posted Thursday, April 25, 2013 11:50 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, December 14, 2014 11:09 PM
Points: 1,962, Visits: 2,406
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/
Post #1446804
Posted Friday, April 26, 2013 12:50 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 1, 2014 11:02 PM
Points: 18, Visits: 274
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
Post #1446814
Posted Friday, April 26, 2013 2:33 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 1:51 AM
Points: 2,693, Visits: 4,755
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/
Post #1446850
Posted Friday, April 26, 2013 3:08 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, December 14, 2014 11:09 PM
Points: 1,962, Visits: 2,406
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/
Post #1446861
Posted Friday, April 26, 2013 3:46 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 1:51 AM
Points: 2,693, Visits: 4,755
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/
Post #1446880
Posted Friday, April 26, 2013 5:16 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, December 14, 2014 11:09 PM
Points: 1,962, Visits: 2,406
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/
Post #1446903
Posted Friday, April 26, 2013 5:17 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, December 14, 2014 11:09 PM
Points: 1,962, Visits: 2,406
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/
Post #1446904
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse