More than one column FOREIGN KEY constraint specified for column

[adammitchell]

How can you indicate that a FOREIGN KEY constraint references two
columns in two different tables?

"SQL Server Books Online" show an example of how to reference two
columns in the SAME table:
REFERENCES ref_table [ ( ref_column [ ,...n ] )

Here is the error and the 'bad' SQL code:

Server: Msg 8148, Level 16, State 1, Line 4
More than one column FOREIGN KEY constraint specified for column
'UserOrGroupId', table 'salesforce3.dbo.AccountShare'.

CREATE TABLE salesforce3.dbo."AccountShare" ("Id" varchar(18) PRIMARY
KEY , ... , "UserOrGroupId" varchar(18) CONSTRAINT
FK_UserOrGroupId6349 FOREIGN KEY REFERENCES "User"(Id) REFERENCES
"Group"(Id) , ... )

[Gert-Jan Strik]

By definition, a Foreign Key can reference only one table. However, you
can define more than one foreign key on the same column, for example:

CREATE TABLE salesforce3.dbo."AccountShare"
("Id" varchar(18) PRIMARY KEY
, ...
, "UserOrGroupId" varchar(18)
, ...
, CONSTRAINT FK_AccountShare_Users FOREIGN KEY (UserOrGroupID)
REFERENCES "User"(Id)
, CONSTRAINT FK_AccountShare_Groups FOREIGN KEY (UserOrGroupID)
REFERENCES "Group"(Id)
)

But if you want to do what I think you want to do, then you are out of
luck. You should not try to give one column two different meanings. If
an AccountShare sometimes has a relation with User and sometimes with
Group, then you should create two columns: one column UserID that
references table User, and one column GroupID that references Group.
Make sure you make both columns nullable.

If you require that an AccountShare is either a User or a Group, then
you can ensure this behavior by adding the following constraint:
CONSTRAINT CK_AccountShare_UserOrGroup
CHECK ( (UserID IS NULL AND GroupID IS NOT NULL)
OR (UserID IS NOT NULL AND GroupID IS NULL) )

Hope this helps,
Gert-Jan
ad**********@marketsquaresolutions.com wrote:

How can you indicate that a FOREIGN KEY constraint references two
columns in two different tables?

"SQL Server Books Online" show an example of how to reference two
columns in the SAME table:
REFERENCES ref_table [ ( ref_column [ ,...n ] )

Here is the error and the 'bad' SQL code:

Server: Msg 8148, Level 16, State 1, Line 4
More than one column FOREIGN KEY constraint specified for column
'UserOrGroupId', table 'salesforce3.dbo.AccountShare'.

CREATE TABLE salesforce3.dbo."AccountShare" ("Id" varchar(18) PRIMARY
KEY , ... , "UserOrGroupId" varchar(18) CONSTRAINT
FK_UserOrGroupId6349 FOREIGN KEY REFERENCES "User"(Id) REFERENCES
"Group"(Id) , ... )

[Patrick]

Here's how I did it a while back. Define all Pks in the main tables when
creating. Then define the FKs in the child tables. Then make the combined
attributes a PK made of the 2 Fks. It worked for me.

I was trying to combine 2 FKs into one PK, and SQL Server won't take it like
that. you can do PK(field1, field2) though, and the 2 fields can be FKs..

ALTER TABLE [dbo].[IS_REGISTERED] ADD
CONSTRAINT [FK_IS_REGISTERED_SECTION] FOREIGN KEY
(
[Section_ID]
) REFERENCES [dbo].[SECTION] (
[Section_ID]
),
CONSTRAINT [STUDENT_IS_REGISTERED] FOREIGN KEY
(
[Student_ID]
) REFERENCES [dbo].[STUDENT] (
[Student_ID]
)
GO

ALTER TABLE [dbo].[IS_QUALIFIED] ADD
CONSTRAINT [COURSES_IS_QUALIFIED] FOREIGN KEY
(
[Course_ID]
) REFERENCES [dbo].[COURSE] (
[Course_ID]
),
CONSTRAINT [FACULTY_IS_QUALIFIED] FOREIGN KEY
(
[Faculty_ID]
) REFERENCES [dbo].[FACULTY] (
[Faculty_ID]
)
GO

ALTER TABLE [dbo].[SECTION] ADD
CONSTRAINT [FK_SECTION_COURSE] FOREIGN KEY
(
[Course_ID]
) REFERENCES [dbo].[COURSE] (
[Course_ID]
)
GO

ALTER TABLE IS_REGISTERED ADD
CONSTRAINT [PK_IS_REGISTERED_STUDENT] PRIMARY KEY(Student_ID, Section_ID)

GO

ALTER TABLE IS_QUALIFIED ADD
CONSTRAINT [PK_IS_QUALIFIED_FACULTY] PRIMARY KEY(Faculty_ID, Course_ID)
<ad**********@marketsquaresolutions.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

How can you indicate that a FOREIGN KEY constraint references two
columns in two different tables?

"SQL Server Books Online" show an example of how to reference two
columns in the SAME table:
REFERENCES ref_table [ ( ref_column [ ,...n ] )

Here is the error and the 'bad' SQL code:

Server: Msg 8148, Level 16, State 1, Line 4
More than one column FOREIGN KEY constraint specified for column
'UserOrGroupId', table 'salesforce3.dbo.AccountShare'.

CREATE TABLE salesforce3.dbo."AccountShare" ("Id" varchar(18) PRIMARY
KEY , ... , "UserOrGroupId" varchar(18) CONSTRAINT
FK_UserOrGroupId6349 FOREIGN KEY REFERENCES "User"(Id) REFERENCES
"Group"(Id) , ... )