Regarding Referential Integrity In SQL 2000

• saurabh.punn

  SSC Enthusiast

  Points: 181

  More actions

  August 5, 2008 at 7:31 am

  #190652

  Hi All.

  I was working on cascading referential integrity constraints in SQL Server.

  Every thing worked fine but i got stuck up with the ON DELETE SET NULL clause.I tried to create a table(test_department) which references a table(department) as:

  create table test_department

  (

  lect_no integer not null primary key,

  lect_name char(20),

  dept_no integer,

  foreign key(dept_no) references department(dept_no) ON DELETE SET NULL

  )

  But it showed the following error:

  Server: Msg 156, Level 15, State 1, Line 6

  Incorrect syntax near the keyword 'SET'.

  I created the department table as:

  create table department

  (

  dept_no integer not null primqry key,

  dept_name char(20)

  )

  I went through lot of articles on referential integrity just to check if there may be some other option but i got the same.

  I am using SQL 2000 client in my local machine.

  Please help me out with this issue.

  Thanks in Advance 🙂

  Saurabh

• Andras Belokosztolszki

  SSC-Insane

  Points: 22031

  More actions

  August 5, 2008 at 8:55 am

  #853236

  Interesting, the above statement should work, and it does work when I try it on a 2000, 2005, 2005 compatibility modes 65,70,80.

  What version and edition of SQL Server do you have? Could you send us the result of:

  sp_dbcmptlevel 'myDBName'

  go

  select @@version

  Regards,

  Andras

  ---

  Andras Belokosztolszki, MCPD, PhD
  GoldenGate Software

• saurabh.punn

  SSC Enthusiast

  Points: 181

  More actions

  August 5, 2008 at 2:59 pm

  #853512

  Hi All

  Tis is the result:

  "sp_dbcmptlevel 'myDBName'

  go

  select @@version"

  The current compatibility level is 80.

  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

  Microsoft SQL Server 2000 - 8.00.194 (Intel X86)

  Aug 6 2000 00:57:48

  Copyright (c) 1988-2000 Microsoft Corporation

  Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

  (1 row(s) affected)

  Thanks

• Andras Belokosztolszki

  SSC-Insane

  Points: 22031

  More actions

  August 6, 2008 at 3:42 am

  #853686

  saurabh.punn (8/5/2008)

  ---

  Hi All

  Tis is the result:

  "sp_dbcmptlevel 'myDBName'

  go

  select @@version"

  The current compatibility level is 80.

  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

  Microsoft SQL Server 2000 - 8.00.194 (Intel X86)

  Aug 6 2000 00:57:48

  Copyright (c) 1988-2000 Microsoft Corporation

  Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

  (1 row(s) affected)

  Thanks

  Ooops, my fault (too many servers + some lame excuse). SET NULL is a feature introduced in SQL Server 2005. Compatibility modes are strange things, and they were accepting the syntax, but on the 2000 server you cannot use "SET NULL" in foreign keys. The only options are "CASCADE" or "NO ACTION"

  Regards,

  Andras

  ---

  Andras Belokosztolszki, MCPD, PhD
  GoldenGate Software

• saurabh.punn

  SSC Enthusiast

  Points: 181

  More actions

  August 6, 2008 at 9:35 am

  #853924

  HI All

  thanks for clearing the doubt.