Error Message when creating DB
-
Server: Msg 1785, Level 16, State 1, Line 125
Introducing FOREIGN KEY constraint 'FK__Completed__Categ__1CF15040' on table 'Completed_Categories' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Server: Msg 1750, Level 16, State 1, Line 125
Could not create constraint. See previous errors.
Here is the code for that table. What is the problem??? I don't understand the problem
CREATE TABLE FAE.dbo.Completed_Categories(
CCID int NOT NULL IDENTITY PRIMARY KEY,
EmployeeTestID int NOT NULL FOREIGN KEY REFERENCES dbo.EmployeeTests
ON DELETE CASCADE,
CategoryID smallint NOT NULL FOREIGN KEY REFERENCES dbo.Categories
ON DELETE CASCADE,
StartTime smalldatetime NULL,
FinishTIme smalldatetime NULL,
Comment varchar(2000) NULL
)
-
Sounds like IF you leave the ON DELETE CASCADE you will wind up in a viscious endless loop. I would look and see if there are triggers that are already in your system that do this and then do what SQL is suggesting ....
Good Hunting!AJ Ahrens
webmaster@kritter.net -
can you post the DDL of EmployeeTests and Categories ?
* Noel -
what the error means is that when using cascades you are not allowed to have more than one possible path for the cascade action to occur!!
ex
T1 <------------------------------ T2
^ |
|-----------------T3 <-------------|
In the above if all relations were defined with cascade when modifiying T2 cacades could be done through T3 or directly from T2 and that is NOT allowed
* Noel -
Hehe, I learn something new everyday... espicially today. I didn't know that the server was validating this... but then again I don't use on cascade delete very often.
-
CREATE TABLE FAE.dbo.EmployeeTests
(
EmployeeTestID int NOT NULL IDENTITY PRIMARY
KEY,
TestID tinyint NOT NULL FOREIGN KEY
REFERENCES dbo.Tests
ON DELETE CASCADE,
EmpID smallint NOT NULL FOREIGN KEY
REFERENCES
dbo.Employees
ON DELETE CASCADE,
EmpTestStartDate smalldatetime NULL,
EmpTestFinishDate smalldatetime NULL
)
CREATE TABLE FAE.dbo.Completed_Categories
(
CCID int NOT NULL IDENTITY PRIMARY KEY,
EmployeeTestID int NOT NULL FOREIGN KEY REFERENCES
dbo.EmployeeTests
ON DELETE CASCADE,
CategoryID smallint NOT NULL FOREIGN KEY REFERENCES
dbo.Categories
ON DELETE CASCADE,
StartTime smalldatetime NULL,
FinishTIme smalldatetime NULL,
Comment varchar(2000) NULL
)
-
Still missing Categories
* Noel -
it comes very handy in Inventory control systems but you have to be really careful and make sure all DML access is through SP because it can get ugly very quickly... it is very powerful but with power comes resposibility
* Noel -
Here is the script for all the tables. Thanks guys!
Use FAE
GO
CREATE TABLE FAE.dbo.Centers
(
CenterID tinyint NOT NULL IDENTITY PRIMARY KEY,
Center varchar(30) NOT NULL,
Inactive bit NOT NULL
)
CREATE TABLE FAE.dbo.Departments
(
DeptID tinyint NOT NULL IDENTITY PRIMARY KEY,
Department varchar(30) NOT NULL,
Inactive bit NOT NULL
)
CREATE TABLE FAE.dbo.Employees
(
EmpID smallint NOT NULL IDENTITY PRIMARY KEY,
FirstName varchar(30) NOT NULL,
LastName varchar(30) NOT NULL,
BadgeNumber char(5) NOT NULL,
DeptID tinyint NOT NULL FOREIGN KEY REFERENCES
dbo.Departments
ON DELETE CASCADE,
CenterID tinyint NOT NULL FOREIGN KEY REFERENCES
dbo.Centers
ON DELETE CASCADE,
Inactive bit NOT NULL
)
CREATE TABLE FAE.dbo.Tests
(
TestID tinyint NOT NULL IDENTITY PRIMARY KEY,
TestName varchar(30) NOT NULL
)
CREATE TABLE FAE.dbo.Categories
(
CategoryID smallint NOT NULL IDENTITY PRIMARY KEY,
TestID tinyint NOT NULL FOREIGN KEY REFERENCES
dbo.Tests
ON DELETE CASCADE,
Category varchar(30) NOT NULL,
CategoryPurpose varchar(300) NULL,
SelectionNumber tinyint NOT NULL,
Inactive bit NOT NULL
)
CREATE TABLE FAE.dbo.Resources
(
ResourceID smallint NOT NULL IDENTITY PRIMARY KEY,
CategoryID smallint NOT NULL FOREIGN KEY
REFERENCES
dbo.Categories
ON DELETE CASCADE,
ResourceTitle varchar(30) NOT NULL,
FileName varchar(100) NOT NULL,
FileType char(4) NOT NULL,
RevisionDate smalldatetime NOT NULL CONSTRAINT
df_RevisionDate
DEFAULT getdate()
)
CREATE TABLE FAE.dbo.Learning
(
LearningID smallint NOT NULL IDENTITY PRIMARY
KEY,
CategoryID smallint NOT NULL FOREIGN KEY
REFERENCES
dbo.Categories
ON DELETE CASCADE,
LearningObjective varchar(200) NOT NULL,
RevisionDate smalldatetime NOT NULL CONSTRAINT
df_LearningRevDate
DEFAULT getdate()
)
CREATE TABLE FAE.dbo.Questions
(
QID smallint NOT NULL IDENTITY PRIMARY KEY,
CategoryID smallint NOT NULL FOREIGN KEY REFERENCES
dbo.Categories
ON DELETE CASCADE,
Question varchar(450) NOT NULL,
Reference varchar(20) NOT NULL,
RevisionDate smalldatetime NOT NULL CONSTRAINT
df_QuestionsRevDate
DEFAULT getdate(),
Inactive bit NOT NULL
)
ALTER TABLE FAE.dbo.Questions
ADD CONSTRAINT df_NoReference DEFAULT 'Not Available' FOR Reference
CREATE TABLE FAE.dbo.Answers
(
AnswerID smallint NOT NULL IDENTITY PRIMARY KEY,
QID smallint NOT NULL FOREIGN KEY REFERENCES
dbo.Questions
ON DELETE CASCADE,
Answer varchar(200) NOT NULL,
Correct bit NOT NULL,
RevisionDate smalldatetime NOT NULL CONSTRAINT
df_AnswersRevDate
DEFAULT getdate()
)
CREATE TABLE FAE.dbo.Diagrams
(
DiagramID smallint NOT NULL IDENTITY PRIMARY KEY,
QID smallint NOT NULL FOREIGN KEY REFERENCES dbo.Questions
ON DELETE CASCADE,
FileName varchar(100) NOT NULL,
DiagramDesc varchar(300) NULL,
RevisionDate smalldatetime NOT NULL CONSTRAINT
df_DiagramsRevDate
DEFAULT getdate()
)
CREATE TABLE FAE.dbo.EmployeeTests
(
EmployeeTestID int NOT NULL IDENTITY PRIMARY KEY,
TestID tinyint NOT NULL FOREIGN KEY
REFERENCES dbo.Tests
ON DELETE CASCADE,
EmpID smallint NOT NULL FOREIGN KEY
REFERENCES
dbo.Employees
ON DELETE CASCADE,
EmpTestStartDate smalldatetime NULL,
EmpTestFinishDate smalldatetime NULL
)
CREATE TABLE FAE.dbo.Completed_Categories
(
CCID int NOT NULL IDENTITY PRIMARY KEY,
EmployeeTestID int NOT NULL FOREIGN KEY REFERENCES
dbo.EmployeeTests
ON DELETE CASCADE,
CategoryID smallint NOT NULL FOREIGN KEY REFERENCES
dbo.Categories
ON DELETE CASCADE,
StartTime smalldatetime NULL,
FinishTIme smalldatetime NULL,
Comment varchar(2000) NULL
)
CREATE TABLE FAE.dbo.Completed_Questions
(
CQID int NOT NULL IDENTITY PRIMARY KEY,
QID smallint NOT NULL FOREIGN KEY REFERENCES
dbo.Questions
ON DELETE CASCADE,
CCID int NOT NULL FOREIGN KEY REFERENCES
dbo.Completed_Categories
ON DELETE CASCADE,
Comment varchar(2000) NULL
)
CREATE TABLE FAE.dbo.Responses
(
ResponseID int NOT NULL IDENTITY PRIMARY KEY,
CQID int NOT NULL FOREIGN KEY REFERENCES
dbo.Completed_Questions
ON DELETE CASCADE,
AnswerID smallint NULL FOREIGN KEY REFERENCES
dbo.Answers
ON DELETE CASCADE
)
GO
-
Sorry Noel. I misread. But i sent everything the last time.
-
Yup, that's why I preffer making a single sp that deletes form 7 tables instead of on delete/cascade. This would put me in a situation where a delete of a single row could litterally wipe out the entire database (documentation of the system starting from the server group right down to the code of each object in the system).
Now talk about making some space in no time
. -
My reasoning on this is that if you delete a category, it should delete the questions that belong to it, the answer that belong to those questions.
If there are completed_categories that the employee has completed testing on, since the categoryid is no longer in the Parent table, the completed_categories should be deleted also, their completed questions, and their responses.
Here is a picture of the relationships. They've been revised a little but it is relatively the same.
-
I was reffering to my situation... I didn't check your relations situation.
-
I understand Remi Gregoire. I was just giving more information because my assumptions must be wrong !
-
Jacob,
Sorry I got back a little late but I tend to finish everything that I start
Take a look a this part of the schema!
TESTS <----------------------------- CATEGORIES
| |
| |
| |
V V
EMPLOYEETESTS -----------------------> COMPLETED_CATEGORIES
DO YOU SEE the LOOP now?
Assume you delete a row in Categories. SQL can cascade that to Tests from there to EmployeeTests and from There to Completed_Categories OR simply cascade it directly to COMPLETED_CATEGORIES
Because there is more than one possible path SQL doesn't like it and you will need to change which part will be cascading or change the design
Hope is clear
* Noel
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply