Creating a Foreign Key to another table
-
I have the following as part of a script:
ALTER TABLE dbo.report_review_log WITH NOCHECK ADD
FOREIGN KEY REFERENCES dbo.report_inventory_log( report_id, proc_name, fyear ) ON DELETE NO ACTION
GO
I am creating a table entitled report_review_log. I want three columns to be Foreign Keys to another table; report_inventory_log. These three columns, along with an Identity Field are part of the Primary Key to the report_review_log table.
When I run this part of the script, I get the following error:
Server: Msg 8139, Level 16, State 1, Line 1
Number of referencing columns in foreign key differs from number of referenced columns, table 'dbo.report_review_log'.
I scripted the report_inventory_log table as it already exists and there are no foriegn keys for that table.
Do I have this backwards? Should the Foreign Keys be on the Primary Table? Or I am missing something with scripting a Foreign Key? There can be many report_review_log records to one report_inventory_log record.
Thanks ahead of time.
I wasn't born stupid - I had to study.
-
Try this:
ALTER TABLE dbo.report_review_log WITH NOCHECK ADD
FOREIGN KEY (report_id, proc_name, fyear) REFERENCES dbo.report_inventory_log( report_id, proc_name, fyear ) ON DELETE NO ACTION
GO
Basically, you have to list the fields in the table you are altering so that it knows which fields you mean.
Hope this helps,
Kathi
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor -
Beauty! Thanks
I wasn't born stupid - I had to study.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply