SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Colulmn Reference


Colulmn Reference

Author
Message
kapil_kk
kapil_kk
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3083 Visits: 2766
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/
sql-programmers
sql-programmers
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 Visits: 277
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
Kingston Dhasian
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3693 Visits: 5180
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
kapil_kk
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3083 Visits: 2766
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 Sad

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Kingston Dhasian
Kingston Dhasian
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3693 Visits: 5180
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
kapil_kk
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3083 Visits: 2766
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 Smile

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
kapil_kk
kapil_kk
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3083 Visits: 2766
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/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search