question

murph99 avatar image
murph99 asked

adding a foreign key constraint

I have added some foreign key constraints, but I get an error when trying to use a "reference" table that is a keyword. Here is the statement: ALTER TABLE Alias ADD CONSTRAINT FK_Alias_Case_Aliases FOREIGN KEY (CaseId) REFERENCES [Case] (id) ; and I get this error: Msg 547, Level 16, State 0, Line 1 The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Alias_Case_Aliases". The conflict occurred in database "TechShare-Prosecutor_conversion", table "dbo.Case", column 'Id'. Not sure how I can get this done. That same syntax works for other tables and FKs.
constraint
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Actually, the statement is: ALTER TABLE Alias ADD CONSTRAINT FK_Alias_Case_Aliases FOREIGN KEY (CaseId) REFERENCES [Case] (id) ; [Case] (id) ;
0 Likes 0 ·
This web site runs on voting. Please indicate all helpful answers by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark next to that answer. You can also mark your own answer.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
Best to never use reserved words as object names in SQL Server. But, if you have to, just put brackets around them when referring to them: [Case].
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Yea, I did. It just does not show up in my comment on here for some reason. [case](id) is the last part of the ADD CONSTRAINT statement. And the error message above seems to understand that is a table and a column.
0 Likes 0 ·
murph99 avatar image
murph99 answered
Alrighty, just figured out how to make it work, in casee anyone is interested. I had to add the constraint with NO CHECK and then do another ALTER TABLE to add the CHECK. Not sure why, but this works.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

iainrobertson avatar image
iainrobertson answered
You mean like this? create table t1 (t1_Id int not null primary key) create table t2 (t2_Id int not null, t1_Id int not null) insert t1 values (1) insert t1 values (2) insert t1 values (3) insert t2 values (1,1) insert t2 values (1,2) insert t2 values (1,4) alter table t2 add constraint fk_t2_t1 foreign key (t1_Id) references t1 -- The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "fk_t2_t1". The conflict occurred in database "Stuff", table "dbo.t1", column 't1_Id'. alter table t2 with nocheck add constraint fk_t2_t1 foreign key (t1_Id) references t1 -- Command(s) completed successfully. alter table t2 check constraint fk_t2_t1 -- Command(s) completed successfully. Note that this appears to be successful, even though there is still bad data in the tables. This is because the check is applied to new records only. It's effectively just enabling the constraint. Your initial error suggests that you have data in the tables that violates the FK requirements. You can live with this if you choose to or you can investigate and resolve it. All new records will have to fit the constraint. I'd second the point on never using reserved words for object names. I have direct experience of quite esoteric failures caused as a result.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

murph99 avatar image
murph99 answered
Thanks. BTW, I agree with the reserved words. I am working on a database from a software company that our company just purchased. They have FIVE tables named as reserved words. Not to mention 257 fields declared as UNIQUEIDENTIFIER. Makes my conversion oh so fun. :) Thanks for all y'all's help, I appreciate it.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.