Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Unable to create relationship FK

Author  Topic 

sapator
Constraint Violating Yak Guru

462 Posts
Posted - 2007-11-17 : 10:01:20
Hi.
I get this error when i try to create a relationship in a db diagram (sql 2005)
"'tblActivedir' table saved successfully
'tblClient' table
- Unable to create relationship 'FK_tblClient_tblActivedir1'.
Introducing FOREIGN KEY constraint 'FK_tblClient_tblActivedir1' on table 'tblClient' 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 i have is 2 tables.
1 named client
1 named activedir

In the client table the columns i want to bind with activedirtable are FR1 and DC1
I want to bind them in the ID of the activedir table (both, in different fk relationships) so that they get the id of activedir.
Fr1 has an fk relationship with activedir (pk is activedir' id)
and DC1 exactly the same in another fk.
So i want both columns to comunicate with activedir.
If p.e. activedir has 3 elements (a,b,c) when i delete element a then werever FR1 or DC1 have this element(binded to it's id) then the element will also be deleted (id of the element) from both FR1 and DC1
I don't want to set Delete and Update action to none because i want the element changed or deleted from activedir, to do the same on Fr1 or DC1 or both.
Any help?
Thanks.

rmiao
Master Smack Fu Yak Hacker

7266 Posts
Posted - 2007-11-17 : 21:21:09
Can you script those tables and post there? Looks like there is fkey between them already.
Go to Top of Page

sapator
Constraint Violating Yak Guru

462 Posts
Posted - 2007-11-18 : 03:53:48
How can i script them?
I have to go to sleep now but i will look into your answer and post the script when i wake up.
But i think the problem exists if you have 2 columns on a table and you want both to be fkeys of a pk column in another table.

I'll be back after sleeping :)
Go to Top of Page

sapator
Constraint Violating Yak Guru

462 Posts
Posted - 2007-11-18 : 10:13:55
Ok i found out how you script in 2005, so here it is.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblActivedir]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tblActivedir](
	[Id] [int] NOT NULL,
	[MMG] [nvarchar](30) NOT NULL,
 CONSTRAINT [PK_tblActivedir] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblPsw]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tblPsw](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ClientId] [int] NOT NULL,
	[Username] [nvarchar](30) NOT NULL,
	[Password] [nvarchar](30) NOT NULL,
 CONSTRAINT [PK_tblPsw] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblReports]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tblReports](
	[Reports] [int] IDENTITY(1,1) NOT NULL,
	[Date] [datetime] NOT NULL,
	[ClientId] [int] NOT NULL,
	[Htmltype] [nvarchar](3) NULL,
	[Arxeio] [binary](50) NULL,
 CONSTRAINT [PK_tblReports] PRIMARY KEY CLUSTERED 
(
	[Reports] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblClient]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tblClient](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Companyname] [nvarchar](50) NOT NULL,
	[Contactname] [nvarchar](50) NULL,
	[Phone] [nvarchar](30) NULL,
	[email] [nvarchar](50) NULL,
	[FRSid] [int] NULL,
	[DNSid] [int] NULL,
	[DHCPid] [int] NULL,
	[WINSid] [int] NULL,
	[Comments] [nvarchar](max) NULL,
 CONSTRAINT [PK_tblClient] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblPsw_tblClient1]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblPsw]'))
ALTER TABLE [dbo].[tblPsw]  WITH CHECK ADD  CONSTRAINT [FK_tblPsw_tblClient1] FOREIGN KEY([ClientId])
REFERENCES [dbo].[tblClient] ([ID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblReports_tblClient]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblReports]'))
ALTER TABLE [dbo].[tblReports]  WITH CHECK ADD  CONSTRAINT [FK_tblReports_tblClient] FOREIGN KEY([ClientId])
REFERENCES [dbo].[tblClient] ([ID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblClient_tblActivedir]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblClient]'))
ALTER TABLE [dbo].[tblClient]  WITH NOCHECK ADD  CONSTRAINT [FK_tblClient_tblActivedir] FOREIGN KEY([FRSid])
REFERENCES [dbo].[tblActivedir] ([Id])
GO
ALTER TABLE [dbo].[tblClient] CHECK CONSTRAINT [FK_tblClient_tblActivedir]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblClient_tblActivedir1]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblClient]'))
ALTER TABLE [dbo].[tblClient]  WITH NOCHECK ADD  CONSTRAINT [FK_tblClient_tblActivedir1] FOREIGN KEY([DNSid])
REFERENCES [dbo].[tblActivedir] ([Id])
GO
ALTER TABLE [dbo].[tblClient] CHECK CONSTRAINT [FK_tblClient_tblActivedir1]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblClient_tblActivedir2]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblClient]'))
ALTER TABLE [dbo].[tblClient]  WITH CHECK ADD  CONSTRAINT [FK_tblClient_tblActivedir2] FOREIGN KEY([DHCPid])
REFERENCES [dbo].[tblActivedir] ([Id])
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblClient_tblActivedir3]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblClient]'))
ALTER TABLE [dbo].[tblClient]  WITH CHECK ADD  CONSTRAINT [FK_tblClient_tblActivedir3] FOREIGN KEY([WINSid])
REFERENCES [dbo].[tblActivedir] ([Id])



I had to set "check existing data on creation" to "no"
and Insert and Update statements to "no action" in order to pass the fk's between tblactivedir and tblclient, just to saw you the general idea.

The problem i see, that also exists if i create a new database and test it, is this...

I want a table with a pkey and a description (like tblactivedir) , another table has it's pkey, some othe columns and 2 or 3 or 4 columns that must get the pkey id of the first table (tblactivedir). I cannot undertand why i have a problem. Can't 2 columns of a table reference on the same pkey of another table? If one row of the first table is deleted, then automaticly the columns of the 2nd table that reference this row of the first table will set their values to null. They can do that because they are allowed nulls. Can you answer why sql does not let me reference a pk's table with more than one columns of another table if i set cascading and deleting options to other than "take no action"? I don't want my Updates and deletes to be set at "no action", i want to insert nulls when a row is deleted and cascade when Updated.
It won't break any referentian integrity. If a row on the first table is deleted then the referenced rows on the other table will be set to null. What is the problem with that?
Thanks.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site