SQL Exception error #547 & Foreign Key constraint violations while attempting a delete

Question

I'm looking at some code (I didn't write it!) to test that our code catches two error conditions upon attempting to delete a table row where dependencies exist.

The code originally looked for specific text in the message rather than using the error number.

In order to provide multi language support it would be better to catch the exception based on the error number rather than the error message.

In the test there are two sets of text the code is looking for and I can't seem to ascertain what the difference is, so am uncomfortable just checking for error number 547.

1. The DELETE statement conflicted with the REFERENCE constraint...
2. The DELETE statement conflicted with the SAME TABLE REFERENCE constraint...

Is it safe to assume that both error messages will have an error number of 547? Cheers Colin

Answer 1

547 is the error code used for any constraint violation, not just foreign keys, e.g.:

create table T (
    ID int not null,
    constraint CK_Not1 CHECK (ID != 1)
)
go
insert into T (ID) values (2)
go
update T set ID = 1


(1 row(s) affected)
Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the CHECK constraint "CK_Not1". The conflict occurred in database "Flange", table "dbo.T", column 'ID'.
The statement has been terminated.

That being said, I can't think of any other type of constraint, other than foreign key, that could be violated by a DELETE statement. (Hat tip to @onedaywhen)

---

If you look in sys.messages, you'll see that 547 has to be for a constraint violation:

select text from sys.messages where message_id=547 and language_id=1033

The %ls statement conflicted with the %ls constraint "%.*ls". The conflict occurred in database "%.*ls", table "%.*ls"%ls%.*ls%ls.

Answer 2

Damien_The_Unbelieve: I can't think of any other type of constraint, other than foreign key, that could be violated by a DELETE statement.

Here's another:

CREATE TABLE T1 (ID INTEGER NOT NULL UNIQUE);
CREATE TABLE T2 
(
 ID INTEGER DEFAULT 0 NOT NULL 
 REFERENCES T1 (ID) ON DELETE SET DEFAULT
 CONSTRAINT cannot_be_zero CHECK (ID <> 0)
);
INSERT INTO T1 (ID) VALUES (1);
INSERT INTO T2 (ID) VALUES (1);
DELETE FROM T1;

Generates the error:

Msg 547, Level 16, State 0, Line 5 The DELETE statement conflicted with the CHECK constraint "cannot_be_zero".