asked on

Error Creating Foreign Keys in SQL Database

Please refer to attached file. I'm currently getting errors when trying to create foreign keys in the attached database design. I'm using SQL Server 2008 R2. The lines represents foreign key relations and the bold represents key fields. In the "HEADER" table, I created a composite key by combining the (COID,RCYEAR,RUNDATE,RUNTIME) fields. From there I'm trying to create the relationships with the remaining tables. Please notice the "COLUMN" table has an additional key field "PPTYPE" that is a foreign key to the "DETAIL" and "PTOTAL" table. I'm running SSIS packages to load the data and they run successfully "before" I create the database relationships. However, when I create the relationships, the packages bombs. Any suggestions on how to properly relate the attached tables. Thanks in advance.
Screenshot_1.PNG

Pawan Kumar

Hi Tyecom,

What error you are facing ?

Below is how you can created foreign key relationship.... Also note that follow this sequence only. Also note that you should primary key on the parent table. E.g. COID -> (PK) in [HEADER]

--

ALTER TABLE [dbo].[COLUMN]  WITH CHECK ADD FOREIGN KEY([COID])
REFERENCES [dbo].[HEADER] ([COID])
GO

ALTER TABLE [dbo].[COLUMN]  WITH CHECK ADD FOREIGN KEY([RCYEAR])
REFERENCES [dbo].[HEADER] ([RCYEAR])
GO

ALTER TABLE [dbo].[COLUMN]  WITH CHECK ADD FOREIGN KEY([RUNTIME])
REFERENCES [dbo].[HEADER] ([RUNTIME])
GO

ALTER TABLE [dbo].[COLUMN]  WITH CHECK ADD FOREIGN KEY([RUNDATE])
REFERENCES [dbo].[HEADER] ([RUNDATE])
GO

ALTER TABLE [dbo].[CTOTAL]  WITH CHECK ADD FOREIGN KEY([COID])
REFERENCES [dbo].[HEADER] ([COID])
GO

ALTER TABLE [dbo].[CTOTAL]  WITH CHECK ADD FOREIGN KEY([RCYEAR])
REFERENCES [dbo].[HEADER] ([RCYEAR])
GO

ALTER TABLE [dbo].[CTOTAL]  WITH CHECK ADD FOREIGN KEY([RUNTIME])
REFERENCES [dbo].[HEADER] ([RUNTIME])
GO

ALTER TABLE [dbo].[CTOTAL]  WITH CHECK ADD FOREIGN KEY([RUNDATE])
REFERENCES [dbo].[HEADER] ([RUNDATE])
GO

ALTER TABLE [dbo].[DETAIL]  WITH CHECK ADD FOREIGN KEY([COID])
REFERENCES [dbo].[COLUMN] ([COID])
GO

ALTER TABLE [dbo].[DETAIL]  WITH CHECK ADD FOREIGN KEY([RCYEAR])
REFERENCES [dbo].[COLUMN] ([RCYEAR])
GO

ALTER TABLE [dbo].[DETAIL]  WITH CHECK ADD FOREIGN KEY([RUNTIME])
REFERENCES [dbo].[COLUMN] ([RUNTIME])
GO

ALTER TABLE [dbo].[DETAIL]  WITH CHECK ADD FOREIGN KEY([RUNDATE])
REFERENCES [dbo].[COLUMN] ([RUNDATE])
GO

ALTER TABLE [dbo].[PTOTAL]  WITH CHECK ADD FOREIGN KEY([COID])
REFERENCES [dbo].[COLUMN] ([COID])
GO

ALTER TABLE [dbo].[PTOTAL]  WITH CHECK ADD FOREIGN KEY([RCYEAR])
REFERENCES [dbo].[COLUMN] ([RCYEAR])
GO

ALTER TABLE [dbo].[PTOTAL]  WITH CHECK ADD FOREIGN KEY([RUNTIME])
REFERENCES [dbo].[COLUMN] ([RUNTIME])
GO

ALTER TABLE [dbo].[PTOTAL]  WITH CHECK ADD FOREIGN KEY([RUNDATE])
REFERENCES [dbo].[COLUMN] ([RUNDATE])
GO


--

Open in new window

Hope it helps!

Tyecom

ASKER

Hello Pawan,

Thank you very much for responding to my question. Are you saying that I should "only" use the COID (in HEADER) as the primary key? My understanding of a foreign key is that it should reference a primary key in another table. This is why I created a composite key that includes the (COID,RCYEAR,RUNDATE,RUNTIME) fields. Are you saying that I should create the foreign keys in the order you have them listed above? Again, thank you very much for assisting me with this issue.

Pawan Kumar

Hi,
I think composite key - (COID,RCYEAR,RUNDATE,RUNTIME) should work fine. Please create composite key and then execute the commands I gave in my last comment.

Are you saying that I should create the foreign keys in the order you have them listed above?
Yes the sequence is important.

Hope it helps!

Tyecom

ASKER

Hi,

When running the above script, this these are the errors messages I received:

Msg 1769, Level 16, State 1, Line 5
Foreign key 'COID' references invalid column 'COID' in referencing table 'COLUMN'.
Msg 1750, Level 16, State 0, Line 5
Could not create constraint. See previous errors.
Msg 1769, Level 16, State 1, Line 2
Foreign key 'RCYEAR' references invalid column 'RCYEAR' in referencing table 'COLUMN'.
Msg 1750, Level 16, State 0, Line 2

Thanks again for your assistance!

Pawan Kumar

Please provide the table columns you are using?

ASKER CERTIFIED SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tyecom

ASKER

Thank you Pawan for your assistance. It worked perfectly!!