Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2002
    Posts
    2

    Unanswered: 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'.

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    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)

  3. #3
    Join Date
    Dec 2002
    Location
    Vancouver, BC
    Posts
    35
    What version of SQL Server are you running?

    ON DELETE CASCADE is only supported in MS SQL Server 2000

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •