July 21, 2006 at 2:22 am
Hi everyone,
I am trying to setup relationships within my database. Here are the tables.
tblCompany
CompanyID
CompanyName
tblProcedure
ProcedureID
ProcedureName
CompanyID1
CompanyID2
When i setup the relationships and enable cascade deleting, i get the below error msg. Why is this?? Am i setting this up incorrectly?
This is the error i get, sorry
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Introducing FOREIGN KEY constraint 'FK_tblProcedure_tblCompany1' on table 'tblProcedure' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint. See previous errors.
My relationships are called FK_tblProcedure_tblCompany and FK_tblProcedure_tblCompany1. Cheers
July 21, 2006 at 4:10 am
Darren
Please will you post the SQL to create those two constraints, and any other foreign key constraints inolving either of those two tables.
Thanks
John
July 21, 2006 at 5:55 am
I havent got the sql, as i am using Diagrams to create the relationships.
CompanyID is an identity field and primary key.
CompanyID1 maybe filled in, as may CompanyID2, but the fields are both optional within tblProcedure.
July 21, 2006 at 6:16 am
Darren
Do you have Query Analyzer? You can use the Object Browser to expand the database, then expand each table. Find the Constraints you are looking for, then right-click and this will give you several scripting options.
It sounds from your first post that you have a foreign key on each table referencing the other. That's why I asked for scripts of all foreign keys on both tables.
John
July 21, 2006 at 6:36 am
i am using sql 2005.
It is because the CompanyID appears twice in tblProcedure?? It that why it wont let me do it?
July 21, 2006 at 7:09 am
No, I don't think so. I think you've got some sort of circular reference going on. But without being able to see what the FKs do, I can't help you. There must be a similar way in SQL 2005 of scripting objects... try searching for it in Books Online.
John
July 21, 2006 at 8:09 am
here is the code to create the objects
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
CREATE
TABLE [dbo].[tblCompany](
[CompanyID] [int]
IDENTITY(1,1) NOT NULL,
[CompanyName] [nvarchar]
(50) COLLATE Latin1_General_CI_AS NULL,
CONSTRAINT [PK_tblCompany] PRIMARY KEY CLUSTERED
(
[CompanyID]
ASC
)
WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
)
ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
CREATE
TABLE [dbo].[tblProcedure](
[ProcedureID] [int]
IDENTITY(1,1) NOT NULL,
[ProcedureName] [nvarchar]
(50) NULL,
[CompanyID1] [int]
NULL,
[CompanyID2] [int]
NULL,
CONSTRAINT [PK_tblPackage] PRIMARY KEY CLUSTERED
(
[ProcedureID]
ASC
)
WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
)
ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER
TABLE [dbo].[tblProcedure] WITH CHECK ADD CONSTRAINT [FK_tblProcedure_tblCompany] FOREIGN KEY([CompanyID1])
REFERENCES
[dbo].[tblCompany] ([CompanyID])
ON
DELETE CASCADE
GO
ALTER
TABLE [dbo].[tblProcerure] CHECK CONSTRAINT [FK_tblProcedure_tblCompany]
This has the CompanyID1 setup, but not CompanyID2 setup as a relationship, how can i do it? Please help. lol
July 21, 2006 at 8:39 am
So I'm guessing you're getting the error message when you try to create the second foreign key constraint? I'm also guessing that you are using the following code to attempt to create it:
ALTER TABLE [dbo].[tblProcedure] WITH CHECK ADD CONSTRAINT [FK_tblProcedure_tblCompany1] FOREIGN KEY([CompanyID2])
REFERENCES
[dbo].[tblCompany] ([CompanyID])
ON
DELETE CASCADE
I think I know what it's saying now. Suppose you successfully create both constraints. Then you delete a row from the tblCompany table. With cascading deletes, that means you automatically delete any related rows in the tblProcedure table. However, since any row in that table may contain two CompanyIDs, you risk deleting a reference to a row in tblCompany that still exists. Therefore it won't let you create the constraint with cascading deletes - I think you'll have work out a business rule for what happens when you lose one CompanyID and create a trigger that executes the rule whenever a delete occurs. Better still, add an extra bit column to tblCompany called Active, which will act as a flag that you can turn off when the company no longer exists. That way you're not actually deleting "old" companies from your table, but you still have a way of knowing that they're not active.
John
July 21, 2006 at 9:01 am
John,
You have hit it on the button, that is exactly what i am trying to do.
But why cant i choose the "SET TO NULL" option, instead of "CASCADE DELETE"? Surely that should be ok, but it wont let me do that either.
I am trying to set up all the relationships within the database so i can enable cascade deleting.
I like the idea of adding a bit field to tblCompany, but if i actually want to delete the company records from the database, i will still have the same problem, wont i?
BTW, thanks for your help so far, usually i find people just dont answer. lol
July 21, 2006 at 9:08 am
Darren
I'm afraid I'm not familiar with the SET TO NULL option. But you can't have cascading deletes for the reason I mentioned above. And you may find that you still want to access historical data in the procedure table, even after the company has ceased to exist (or be a customer of yours). That's why it's always safer to mark inactive instead of to delete. But, as I said, if you do want to delete then I think you will need to define a business rule and enforce it with a trigger.
This is a very good forum for getting responses - I don't see all that many posts go unanswered!
Have a good weekend
John
July 21, 2006 at 9:21 am
EDIT
Let me clear some confusion for you IDENTITY is a property and I could not see CompanyID or CompanyName in table two so your DRI(declarative referential integrity) is invalid. Some of the reason ANSI SQL writers are happy with SQL Server 2005 and Microsoft is updating and correcting the online BOL(books online). The rule is if B references A then A must exist. The links below will help clear your confusion.
SET NULL and SET DEFALUT are new to SQL Server but your constraint must be correct to set any DRI, now a trigger can let you create crude referencing but DRI will not. The reason triggers are vendor creation while DRI is ANSI SQL.
Hope This helps.
http://msdn2.microsoft.com/en-us/library/ms177288.aspx
http://msdn2.microsoft.com/en-us/library/ms186973.aspx
Kind regards,
Gift Peddie
Kind regards,
Gift Peddie
July 21, 2006 at 9:41 am
Gift,
CompanyID is in tblProcedure, CompanyID1 and CompanyID2.
July 21, 2006 at 9:49 am
To get DRI it must still be CompanyID and not CompanyID1 and CompanyID2 so just do ANSI SQL ALTER Table and change the column names to match because in DRI the parent is keeping track of the update and deletes in the child tables so the names must match. Runs a search in the BOL(books online) for Cascade on DELETE and Cascade on Update code samples. Hope this helps.
Kind regards,
Gift Peddie
Kind regards,
Gift Peddie
July 21, 2006 at 9:53 am
Gift,
Ok i will look into it, but you cant have two columns with the same name in the same table?
Sorry if i am being silly.
July 21, 2006 at 10:03 am
You can but you cannot use them in DRI because the columns participating in DRI is keeping track of each other in delete and update so your current table will create confusion in both update and delete. Hope this helps.
Kind regards,
Gift Peddie
Kind regards,
Gift Peddie
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply