Cascade set to null on delete.

Hi all,

I am using SQL Server 2000 and am trying to perform a basic delete query on a table called ClientType. The only child table of this is called Client. The relationship between the tables has a cascade action of cascade update and when I try to perform the delete operation, I get the error "DELETE statement conflicted with COLUMN REFERENCE constraint". The foreign key field accepts nulls and has a defauilt value of null. Now, am I being completely dense or shouldn't the cascade update set the foreign key values to null?

Regards,

Stephen.

Could you please send over the DDL for the table and the constraints, thanks.

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de|||

Here you go:
...

CREATE TABLE [dbo].[ClientType] (
[ClientTypeId] [int] IDENTITY (1, 1) NOT NULL ,
[ObjectVersion] [int] NULL ,
[Type] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Client] (
[ClientId] [int] IDENTITY (1, 1) NOT NULL ,
[ObjectVersion] [int] NULL ,
[ClientTypeId] [int] NULL ,
[ReferenceNumber] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Password] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Title] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Forename] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Initial] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Surname] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateOfBirth] [datetime] NULL ,
[DateRegistered] [datetime] NULL ,
[Address1] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address2] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address3] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[County] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Postcode] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Country] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Telephone1] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Telephone2] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Telephone3] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fax] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EMail] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[URL] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Position] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Overview] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[InternalOverview] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Comments] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CV] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HoldingCompanyName] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[ClientType] WITH NOCHECK ADD
CONSTRAINT [PK_ClientType] PRIMARY KEY CLUSTERED
(
[ClientTypeId]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Client] WITH NOCHECK ADD
CONSTRAINT [PK_Client] PRIMARY KEY CLUSTERED
(
[ClientId]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ClientType] ADD
CONSTRAINT [DF_ClientType_ObjectVersion] DEFAULT (0) FOR [ObjectVersion]
GO

ALTER TABLE [dbo].[Client] ADD
CONSTRAINT [DF_Client_ObjectVersion] DEFAULT (0) FOR [ObjectVersion],
CONSTRAINT [DF_Client_ClientTypeId] DEFAULT (null) FOR [ClientTypeId]
GO

ALTER TABLE [dbo].[Client] ADD
CONSTRAINT [FK_Client_ClientType] FOREIGN KEY
(
[ClientTypeId]
) REFERENCES [dbo].[ClientType] (
[ClientTypeId]
) ON UPDATE CASCADE
GO

...

Regards,

Stephen.

|||OK, you just defined the Cascade on the update, if you delete a row and the child table contains rows for that parent row and you did not define a cascade delete on the parent table, this error message will come up.

You wil have to add: ON DELETE CASCADE

HTH; Jens Suessmeyer.

http://www.sqlserver2005.de|||

Thanks for the response. I do understand that adding cascade delete would get rid of the error but will that not result in the child rows being deleted? I only want the foreign keys to be set to null, not have the entire related record dropped from the table.

Regards,

Stephen.

|||OK, then you will have to use the ON DELETE SET NULL.

HTH; Jens Suessmeyer.

http://www.sqlserver2005.de|||OK. I gave that a go, but I just get an error "incorrect syntax near the keyword 'SET'".|||

Sorry, I re-read your first post and saw that you are using SQL Server 2k. SET NULL is a new feature for SQL 2k5. In SQL Server 2000 you probably would use no constraint in that case and do the work with triggers, in that case an update trigger.

Sorry for confusing you :-)

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

|||

OK. I'll look into that.

Thanks for your help. Its much appreciated.

Regards,

Stephen.