Composite FK with One Primary Key

1

Hi,

I have the following code;


USE [EMISDummyDB]
GO

ALTER TABLE [dbo].[Admin_Patient]  WITH CHECK ADD  CONSTRAINT [FK_Admin_Patient_Admin_Organisation] FOREIGN KEY([OrganisationGuid],[ExternalUsualGPOrganisation])
REFERENCES [dbo].[Admin_Organisation] ([OrganisationGuid])
GO

ALTER TABLE [dbo].[Admin_Patient] CHECK CONSTRAINT [FK_Admin_Patient_Admin_Organisation]
GO

I am trying to have one primary key (OrganisationGuid) from dbo.Admin_Organsiation with a relationship with 2 foreign keys OrganisationGuid,ExternalUsualGPOrganisation on table dbo.Admin_Patient

How can I achieve this ?

The above code give me an error -

Number of referencing columns in foreign key differs from number of referenced columns, table 'dbo.Admin_Patient'

Same error I receive when doing it via the wizard.

Thanks

2

surrogate key

3

The foreign key must be guaranteed to be unique in the parent table; not just exist. The primary key would, of course, be unique but you'll need to define a unique key in the parent table with composite columns. Something along the lines of:CREATE UNIQUE INDEX UQ_MyTable on MyTable(MyPK, MyOtherColumn)

4

Yes, the number of keys must match. You need to specify either 1 or 2 keys on both tables to relate to each other.