472,354 Members | 1,913 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,354 software developers and data experts.

More than one column FOREIGN KEY constraint specified for column

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

Jul 23 '05 #1
2 26530
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) , ... )

Jul 23 '05 #2
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) , ... )

Jul 23 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Gunnar Vřyenli | last post by:
Hi (SQL Server 2000) I have an existing table (t) with a column that is NOT an identity column (t.ID), but it has manually inserted "row numbers". I want to make this column become an identity...
2
by: Beacher | last post by:
Hello, I'm trying to create a relationship between two tables, a one to many tblDeal being the primary table, I'm trying to create a relationship between tblDeal and tblCondition .. i set...
2
by: | last post by:
Hello All, I am having a lot of difficulty trying to bind a templated column, that is programmatically created for a datagrid, to a datasource column. I have a datasource containing 2 columns,...
2
by: Tim::.. | last post by:
Can someone please tell me why I keep getting the following error from the code below! Error: INSERT statement conflicted with COLUMN FOREIGN KEY constraint...
1
by: Ken Varn | last post by:
I have a problem where my DataGrid would not maintain the ViewState of my databound rows. I finally narrowed down the problem. If my first column is a template column, the view state for the...
7
by: am72de | last post by:
Hi all, I've posted this problem some weeks ago, but noone had a solution. Perhaps now someone could help me. I have the following tables: Create Table T1 ( ID1 int Not Null , ID2 int Not...
0
by: chadsmith76 | last post by:
I am trying to find some documentation on using things like ORDER BY 5*(column LIKE ' term') + 4*(column LIKE ' term') I don't even know what it is called so it is difficult to search. My...
31
by: Sarita | last post by:
Hello, this might sound stupid, but I got a really nice homepage template which unfortunately is a 3-Column Fixed Width CSS format. Now I don't have any content for the right column and would...
3
by: NewlytoSQL | last post by:
Hi all, im fairly new to SQL and im stuck half way through a query, im using DB2 here is what im tryng to do. i have a query that brings back an item number , shelf req, sum of all orders columns,...
2
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and efficiency. While initially associated with cryptocurrencies...
0
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge required to effectively administer and manage Oracle...
0
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was proposed, which integrated multiple engines and...
0
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. header("Location:".$urlback); Is this the right layout the...
2
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it so the python app could use a http request to get...
0
by: Matthew3360 | last post by:
Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web server and have made sure to enable curl. I get a...
0
Oralloy
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the synthesis of my design into a bitstream, not the C++...
0
BLUEPANDA
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS starter kit that's not only easy to use but also...
0
by: Rahul1995seven | last post by:
Introduction: In the realm of programming languages, Python has emerged as a powerhouse. With its simplicity, versatility, and robustness, Python has gained popularity among beginners and experts...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.