referential integrity doesn't work

[Prinzxxx]

Hi all.

I am using MsSQL2008 and I am struggling with a problem and I hope there is one out there with knowledge of the basics of DB design:

I have 2 tables. One is a simple lookup table and the other is a table using it. For an example we could call the lookup table Contacts and the other using the Contacts table – just Table1. I would like to use referential integrity so the ‘DB-engine’ handles all the changes correctly.

For example:

The first table “Contacts” consists just of one field (nvarchar 50) which also has a primary key.

here is the CREATE TO script which I have created with using Microsoft SQL Server Manager.



USE [TEST]
GO

/****** Object: Table [dbo].[Contacts] Script Date: 07/03/2010 20:10:42 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Contacts](
[Contact_PK] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Supplier] PRIMARY KEY CLUSTERED
(
[Contact_PK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,

ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Now I want to have a second table which looks up in the lookup table “Contacts”. But I would like to have more than just one field, but two fields. Something like this.:

Primary key
Field1
Field2
ContactPersonID
ExpertID

The 2 ID-fields are foreign keys which I want to relate to the primary key in the first table (Contacts).

With the manager I have created two relationships which are called “FK_Table1_ContactPerson” and
“FK_Table1_Expert”

The CREATE TO script for the two tables is:



USE [TEST]
GO

/****** Object: Table [dbo].[Table1] Script Date: 07/03/2010 20:12:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Table1](
[Tablel1_PK] [int] NOT NULL,
[Field1] [real] NULL,
[Field2] [real] NULL,
[ContactPerson_ID] [nvarchar](50) NULL,
[Expert_ID] [nvarchar](50) NULL,
CONSTRAINT [PK_Spareparts] PRIMARY KEY CLUSTERED
(
[Tablel1_PK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,

ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Table1] WITH CHECK ADD CONSTRAINT [FK_Table1_ContactPerson] FOREIGN KEY

([ContactPerson_ID])
REFERENCES [dbo].[Contacts] ([Contact_PK])
GO

ALTER TABLE [dbo].[Table1] CHECK CONSTRAINT [FK_Table1_ContactPerson]
GO

ALTER TABLE [dbo].[Table1] WITH CHECK ADD CONSTRAINT [FK_Table1_Expert] FOREIGN KEY([Expert_ID])
REFERENCES [dbo].[Contacts] ([Contact_PK])
GO

ALTER TABLE [dbo].[Table1] CHECK CONSTRAINT [FK_Table1_Expert]
GO




Now I want to use referential integrity to update all records correctly.

Means: when I change something in the “Contacts” table, these changes shall get updated in all related ID-fields (“ContactPerson” and “Expert”) in the other table (“Table1)

And when I delete a field in “Contacts” the field in the record of “Table1” shall change to NULL in the ID fields (“ContactPerson” and “Expert”)

Via manager again I opened the first relationship “FK_Table1_ContactPerson” and used the INSERT and UPDATE Specification. "Delete rule" I set to "Set Null" and the "Update rule" I set to "Cascade".

Script is now showing:



USE [TEST]
GO

/****** Object: Table [dbo].[Table1] Script Date: 07/03/2010 20:15:43 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Table1](
[Tablel1_PK] [int] NOT NULL,
[Field1] [real] NULL,
[Field2] [real] NULL,
[ContactPerson_ID] [nvarchar](50) NULL,
[Expert_ID] [nvarchar](50) NULL,
CONSTRAINT [PK_Spareparts] PRIMARY KEY CLUSTERED
(
[Tablel1_PK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,

ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Table1] WITH CHECK ADD CONSTRAINT [FK_Table1_ContactPerson] FOREIGN KEY

([ContactPerson_ID])
REFERENCES [dbo].[Contacts] ([Contact_PK])
ON UPDATE CASCADE
ON DELETE SET NULL
GO

ALTER TABLE [dbo].[Table1] CHECK CONSTRAINT [FK_Table1_ContactPerson]
GO

ALTER TABLE [dbo].[Table1] WITH CHECK ADD CONSTRAINT [FK_Table1_Expert] FOREIGN KEY([Expert_ID])
REFERENCES [dbo].[Contacts] ([Contact_PK])
GO

ALTER TABLE [dbo].[Table1] CHECK CONSTRAINT [FK_Table1_Expert]
GO

Everything worked fine until I made the same settings in the second relation:
“FK_Table1_Expert”
Delete = NULL
Update = cascade

Then I got this error-message:

'Contacts' table saved successfully
'Table1' table
- Unable to create relationship 'FK_Table1_Expert'.
Introducing FOREIGN KEY constraint 'FK_Table1_Expert' on table 'Table1' may cause cycles or multiple cascade
paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Could not create constraint. See previous errors.

What did I do wrong?