Error 547

Severity

Message text

Dependent foreign key constraint violation in a referential integrity constraint. dbname =  '%.*s', table name = '%.*s',  constraint name = '%.*s'.%S_EED

Explanation

Adaptive Server provides integrity constraints to help you maintain logical data integrity in a database. Referential integrity (or foreign key) constraints are a type of constraint which require that data being inserted into a given table column already has matching data in another column (the target column), which may be in the same table or another table. The column on which the constraint is declared can be considered the child, or dependent, in a parent-child relationship.

Error 547 is raised when updating or deleting rows from a parent table would remove target columns matching dependent data in the child table. For example:

1> create table parent
2> (a int primary key,
3> b int,
4> unique (b))
5> go

1> create table child
2> (c int primary key,
3> d int references parent(b))
4> go

1> insert parent values (11,22)
2> insert parent values (13,26)
3> insert child  values (101,22)
4> go

1> delete parent where a=13
2> go

(1 row affected)

1> delete parent where a=11
2> go

Msg 547, Level 16, State 1: Line 1:
Dependent foreign key constraint violation in a referential integrity
constraint. dbname =  'hrdb', table name = 'parent', constraint name =
'detail_d_1088006907'.
Command has been aborted.
(0 rows affected)

1> update parent
2> set b=29
3> where a=11
4> go

Msg 547, Level 16, State 1: Line 1:
Dependent foreign key constraint violation in a referential integrity
constraint. dbname =  'hrdb', table name = 'parent', constraint name =
'child_d_1088006907'.
Command has been aborted.
(0 rows affected)

Notice that you may drop or update rows in parent provided you do not affect the referential integrity constraints.

Action

Delete or update the dependent data in the child table before deleting or changing the parent data.

Additional information

Use the system procedure sp_helpconstraint to view the referential constraints in affect for a table. See the Reference Manual: Procedures for detail about using the sp_helpconstraint procedure.

Versions in which this error is raised

All versions