can someone explain why im getting this error with sql query

Question

create table Department (
Dep_ID int not null,
Dep_Name varchar(30),
primary key (Dep_ID),
)

create table Course (
C_ID int not null,
C_Name varchar (30) not null,
C_Duration varchar (10) not null,
DegreeType varchar (20),
Dep_ID int,
primary key (C_ID),
constraint DEP_ID1 foreign key (Dep_ID) references Department (Dep_ID) on update cascade,
)


create table Student (
St_ID int not null,
St_Name varchar (100),
St_age smallint,
St_gender Varchar(6),
St_tel int,
St_ADD varchar (100) not null,
St_city varchar (50)not null,
St_type varchar (20) not null,
St_nationality varchar (5) not null,
Dep_ID int,
C_ID int,
primary key (St_ID),
constraint DEP_ID foreign key (Dep_ID) references Department(Dep_ID) on update cascade,
constraint CO_ID foreign key (C_ID) references Course(C_ID) on update cascade,

)

create table Staff (
Sta_ID int not null,
Sta_Name varchar (100) not null,
Sta_type varchar (20) not null,
Sta_Add varchar (100) not null,
Sta_tel int ,
Dep_ID int,
primary key (Sta_ID),
constraint DEeP_ID foreign key (Dep_ID) references Department (Dep_ID) on update cascade,
)

this is the error im getting why cant i use cascade update on composite keys

Msg 1785, Level 16, State 0, Line 19 Introducing FOREIGN KEY constraint 'CO_ID' on table 'Student' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Msg 1750, Level 16, State 0, Line 19 Could not create constraint. See previous errors.

Could you post the definition of your `Course` and `Department` table, please? — kkuilla, Mar 18 '15 at 09:07
There is a problem with foreign key constraint as the error says, post the ddl of your tables Course and Department — Nightmaresux, Mar 18 '15 at 09:21
create table Department ( Dep_ID int not null, Dep_Name varchar(30), primary key (Dep_ID), ) create table Course ( C_ID int not null, C_Name varchar (30) not null, C_Duration varchar (10) not null, DegreeType varchar (20), Dep_ID int, primary key (C_ID), constraint DEP_ID foreign key (Dep_ID) references Department (Dep_ID) on update cascade, ) — Jerome Jacobs Francis, Mar 18 '15 at 09:34

score 0 · Answer 1 · answered Mar 18 '15 at 09:34

Your problem is in table 'Exam'. Since you specified no source for this table I can only guess ;-)

Exam contains a foreign key constraint Course_ID2

It seems that this foreign key also refrences to Department or Course.

So have a look into this constraint first. If you need further assistance, please post source of table definition Exam, Department and Course

Pankaj katiyar · Answer 2 · 2015-03-18T09:52:12.900

When you are adding foreign key

They need to be exactly the same data type in both tables.

Try with your query it works fine with me i just remove "," from end of your column

CREATE TABLE Department (
Dep_ID INT NOT NULL,
Dep_Name VARCHAR(30),
PRIMARY KEY (Dep_ID)
)

CREATE TABLE Course (
C_ID INT NOT NULL,
C_Name VARCHAR (30) NOT NULL,
C_Duration VARCHAR (10) NOT NULL,
DegreeType VARCHAR (20),
Dep_ID INT,
PRIMARY KEY (C_ID),
CONSTRAINT DEP_ID1 FOREIGN KEY (Dep_ID) REFERENCES Department (Dep_ID) ON UPDATE CASCADE
)


CREATE TABLE Student (
St_ID INT NOT NULL,
St_Name VARCHAR (100),
St_age SMALLINT,
St_gender VARCHAR(6),
St_tel INT,
St_ADD VARCHAR (100) NOT NULL,
St_city VARCHAR (50)NOT NULL,
St_type VARCHAR (20) NOT NULL,
St_nationality VARCHAR (5) NOT NULL,
Dep_ID INT,
C_ID INT,
PRIMARY KEY (St_ID),
CONSTRAINT DEP_ID FOREIGN KEY (Dep_ID) REFERENCES Department(Dep_ID) ON UPDATE CASCADE,
CONSTRAINT CO_ID FOREIGN KEY (C_ID) REFERENCES Course(C_ID) ON UPDATE CASCADE

)

CREATE TABLE Staff (
Sta_ID INT NOT NULL,
Sta_Name VARCHAR (100) NOT NULL,
Sta_type VARCHAR (20) NOT NULL,
Sta_Add VARCHAR (100) NOT NULL,
Sta_tel INT ,
Dep_ID INT,
PRIMARY KEY (Sta_ID),
CONSTRAINT DEeP_ID FOREIGN KEY (Dep_ID) REFERENCES Department (Dep_ID) ON UPDATE CASCADE
)

no i get this error when i execute the student table Msg 1785, Level 16, State 0, Line 1 Introd FOREIGN KEY constraint 'CO_ID' on table 'Student' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Msg 1750, Level 16, State 0, Line 1 Could not create constraint. See previous errors. ucing — Jerome Jacobs Francis, Mar 18 '15 at 09:59
dont you think its because of the Dep_ID a composite key and its used cascade two time its what the error says right — Jerome Jacobs Francis, Mar 18 '15 at 10:03

score 0 · Accepted Answer · edited May 23 '17 at 12:09

I had done a research and get to know that this problem can be resolve by changing the line : constraint CO_ID foreign key (C_ID) references Course(C_ID) on update cascade, to constraint CO_ID foreign key (C_ID) references Course(C_ID) on update No Action,

I have get a explanation on the following link : Foreign key constraint may cause cycles or multiple cascade paths?

This may resolve your problem.

can someone explain why im getting this error with sql query

3 Answers3