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.
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.
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.