June 24, 2008 at 2:48 pm
I have a design strategy question. I have a database in SS2005. There is a main table that has two fields that both link to the same primary key field in another table. So my plan was to link each field in the main table to the field in the secondary table with two different foreign key relationships.
For example:
Table1.A ---> Table2.C As FK_A_C
Table1.B ---> Table2.C AS FK_B_C
However, when I try to create these relationships, SS2005 will not let me save them. Do I need an intermediary/crosswalk table?
If we needed to do a query we could just create two instances of the same table so I do not understand what rule I am breaking.
It appears that I am having the same problem as this thread (Introducing FOREIGN KEY constraint on table may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.):
http://www.sqlservercentral.com/Forums/Topic494273-145-1.aspx
Thanks,
cj
June 26, 2008 at 5:06 pm
According to the description in your post, this kind of relationship is possible
You should explain what kind of error SQL is giving you when you try and "save" these tables (?).
Though you have described the logical model somewhat there is not enough information to determine if this foreign key will work in your actual scenario.
Remember that a foreign key must identify a unique row in the primary table.
Example Code:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_2](
[C] [int] NOT NULL,
CONSTRAINT [PK_Table_2] PRIMARY KEY CLUSTERED
(
[C] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_1](
[A] [int] NOT NULL,
[int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table_1] WITH CHECK ADD CONSTRAINT [FK_Table_1_Table_2] FOREIGN KEY([A])
REFERENCES [dbo].[Table_2] ([C])
GO
ALTER TABLE [dbo].[Table_1] CHECK CONSTRAINT [FK_Table_1_Table_2]
GO
ALTER TABLE [dbo].[Table_1] WITH CHECK ADD CONSTRAINT [FK_Table_1_Table_21] FOREIGN KEY()
REFERENCES [dbo].[Table_2] ([C])
GO
ALTER TABLE [dbo].[Table_1] CHECK CONSTRAINT [FK_Table_1_Table_21]
June 27, 2008 at 6:59 pm
There is nothing wrong with what you are trying to do. It is done all the time, say a table has a SentBy and a SentTo column both referring to a People table.
Without knowing the text of the error, it's kinda hard to tell what is going wrong. If you get an error msg saying it can't create FK_A_C because it conflicts with FK_A_C, then you have bogus info in that column.
Before creating foreign keys on tables with existing data, run the following query:
select t1.*
from dbo.Table1 t1
left join dbo.Table2 t2
on t1.A = t2.C
where t2.C is null;
Repeat for Table1.B column.
If you get any results from the query, that means there are values in column A (or B) that don't refer to an existing value of C in Table2.
Or it could be that column C is not defined as the Primary Key of Table2 or does not have a unique constraint.
Or...
(Letting us know the text of the error would help a bit.)
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply