Monday, March 26, 2012

Problem with sql

I've got 2 table :department and employee. There's a column called deptNo reference employee to department as a foreign key.

1) When I want to delete the employee table, it gives me this error:
Server: Msg 3726, Level 16, State 1, Line 1
Could not drop object 'Employee' because it is referenced by a FOREIGN KEY constraint.

2) If I enter the statement below:
CREATE TABLE Employee(EmployeeNo int PRIMARY KEY, EmployeeName varchar(10)UNIQUE, DeptNo int NOT NULL, JobTitle char(10),FOREIGN KEY(DeptNo)REFERENCES Department(DeptNo)ON UPDATE CASCADE ON DELETE CASCADE)
it gives me an error:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'ON'.It looks like you had the wrong direction for your constraint, which is what you might be trying to correct. To eliminate the error you can delete the foreign key constraint (since you are recreating the table and constraint). For the 2nd part, try the following (I modified your version slightly) :

CREATE TABLE Employee(EmployeeNo int PRIMARY KEY, EmployeeName varchar(10)UNIQUE,
JobTitle char(10),deptno int NOT NULL FOREIGN KEY REFERENCES Department(DeptNo)ON UPDATE CASCADE ON DELETE CASCADE)|||What version of SQL Server are you running?

ON DELETE CASCADE is only supported in MS SQL Server 2000

No comments:

Post a Comment