stubborn constraint error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

stubborn constraint error

Author

Topic

rb1373
Yak Posting Veteran

93 Posts

Posted - 2006-07-19 : 15:27:30

Hello all!

I am deleting from a child table, then from the parent table but a stubborn constraint error keeps appearing:

Server: Msg 547, Level 16, State 1, Line 4
DELETE statement conflicted with COLUMN REFERENCE constraint 'FK911_notepad'. The conflict occurred in database 'Portal', table 'notepad_log', column 'notepad_id'.

I have tried disabling the constraints but the error still appears.

Any idea how I can get around this error?

Thanks,
Ray

--- DML test script that generates the error
begin tran

DECLARE @InsurerNumber VARCHAR(10)

--======================
SET @InsurerNumber = '15'
--======================

CREATE TABLE #DeleteClaims (
claim_id INT
)

-- delete from
INSERT #DeleteClaims
SELECT c.claim_id
FROM dbo.claim c
join dbo.claimant ct on c.claim_id = ct.claim_id
JOIN dbo.multiple_policy_claim mpc ON c.claim_id = mpc.claim_id
JOIN dbo.policy policy ON mpc.policy_id = policy.policy_id
WHERE insurer_number IN (@InsurerNumber)

ALTER TABLE notepad_log NOCHECK CONSTRAINT ALL
ALTER TABLE notepad_log disable TRIGGER ALL
SET ROWCOUNT 1000
delete_more:
DELETE notepad_log WHERE claim_id IN (SELECT claim_id FROM #DeleteClaims)
IF @@ROWCOUNT > 0 GOTO delete_more
SET ROWCOUNT 0
ALTER TABLE notepad_log enable TRIGGER ALL
ALTER TABLE notepad_log WITH CHECK CHECK CONSTRAINT ALL

ALTER TABLE notepad NOCHECK CONSTRAINT ALL
ALTER TABLE notepad disable TRIGGER ALL
SET ROWCOUNT 1000
delete_more1:
DELETE notepad WHERE claim_id IN (SELECT claim_id FROM #DeleteClaims)
IF @@ROWCOUNT > 0 GOTO delete_more1
SET ROWCOUNT 0
ALTER TABLE notepad enable TRIGGER ALL
ALTER TABLE notepad WITH CHECK CHECK CONSTRAINT ALL

--COMMIT TRAN
--ROLLBACK TRAN

--DROP TABLE #DeleteClaims

--- DDL of the 2 tables involved
CREATE TABLE [dbo].[notepad_log] (
[notepad_id] [int] NOT NULL ,
[log_id] [int] NOT NULL ,
[claimant_id] [int] NULL ,
[claim_id] [int] NULL ,
[edit_date] [datetime] NOT NULL ,
[edit_user] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE CLUSTERED INDEX [X911_edit_user_x] ON [dbo].[notepad_log]([edit_user], [notepad_id]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[notepad_log] ADD
CONSTRAINT [DF__notepad_l__edit___46A92998] DEFAULT (getdate()) FOR [edit_date],
CONSTRAINT [DF__notepad_l__edit___24891BBE] DEFAULT (user_name()) FOR [edit_user],
CONSTRAINT [XPK_notepad_log] PRIMARY KEY NONCLUSTERED
(
[log_id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[notepad_log] ADD
CONSTRAINT [FK911_notepad] FOREIGN KEY
(
[notepad_id]
) REFERENCES [dbo].[notepad] (
[notepad_id]
)
GO

CREATE TABLE [dbo].[notepad] (
[notepad_id] [int] NOT NULL ,
[claimant_id] [int] NULL ,
[claim_id] [int] NULL ,
[insurance_type] [tinyint] NOT NULL ,
[client_code] [tinyint] NOT NULL ,
[notepad_type_code] [tinyint] NOT NULL ,
[overview] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[body] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[add_date] [datetime] NOT NULL ,
[add_user] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[edit_date] [datetime] NOT NULL ,
[edit_user] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[siu] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[amount] [money] NULL ,
[confidentiality_code] [int] NULL ,
[incident_level] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[notepad_related_id] [int] NULL ,
[notepad_related_name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[notepad] ADD
CONSTRAINT [DF__notepad__add_dat__7E2E68AC] DEFAULT (getdate()) FOR [add_date],
CONSTRAINT [DF__notepad__add_use__5B1A3699] DEFAULT (user_name()) FOR [add_user],
CONSTRAINT [DF__notepad__edit_da__45B5055F] DEFAULT (getdate()) FOR [edit_date],
CONSTRAINT [DF__notepad__edit_us__2394F785] DEFAULT (user_name()) FOR [edit_user],
CONSTRAINT [XPK_notepad] PRIMARY KEY NONCLUSTERED
(
[notepad_id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[notepad] ADD
CONSTRAINT [FK872_claim] FOREIGN KEY
(
[claim_id]
) REFERENCES [dbo].[claim] (
[claim_id]
),
CONSTRAINT [FK872_confidentiality_code] FOREIGN KEY
(
[confidentiality_code]
) REFERENCES [dbo].[confidentiality] (
[confidentiality_code]
),
CONSTRAINT [FK872_notepad_type] FOREIGN KEY
(
[notepad_type_code],
[insurance_type],
[client_code]
) REFERENCES [dbo].[notepad_type] (
[notepad_type_code],
[insurance_type],
[client_code]
)
GO

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-07-20 : 06:15:59

It seems that foreign key relationship between the parent & child table is getting violated by the delete operation. Ensure that when u delete from parent table, all child records for that record is deleted from child table.

Harsh Athalye
India.
"Nothing is Impossible"

rb1373
Yak Posting Veteran

93 Posts

Posted - 2006-07-20 : 11:09:46

All the children are deleted.

This error even appears when I have disabled ALL the constraints in the database. If there are no constraints, why would the error appear when a delete occurs???

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources