update or delete violates foreign key constraint

Hallo,

ich habe folgende zwei Tabellen in meiner Datenbank

create table foo (
  id serial primary key,
  ...
);

create table bar (
  id serial primary key,
  foo serial references foo(id),
);

Nun habe ich in Tabelle bar einen Datensatz erstellt, der einen anderen Datensatz in foo ueber die ‘foo’ Spalte referenziert.

Wie kann ich nun den Datensatz aus foo, welcher aus der bar Tabelle noch referenziert wird inklusive aller referenzierenden Datensaetze loeschen?

Ein einfaches

delete from foo where id = ? ;

zieht naemlich folgende Fehlermeldung nach sich:

update or delete on “foo” violates foreign key constraint “bar_foo_fkey” on “bar”.
DETAIL: Key (id)=(?) is still referenced from table “bar”.

Vielen Dank im Voraus,
Flo

hi flo,

du kannst bei REFERNCE nocht angeben was er bei updates und deletes machen soll…

hier also z.b.

foo int REFERENCE foo(id) ON UPDATE CASCADE ON DELETE CASCADE

hier würden updates und deletes durchschlagen. mit RESTRICT kannst du das verhindern und z.b. dein o.g. verhalten erzeugen…

cu tb.

D.h. RESTRICT ist der default und ich kann, wenn ich es fuer noetig halte, CASCADE verwenden?

Oder gibt es noch eine genauere Unterscheidung zwischen

a) keine weitere Angabe
b) Angabe von RESTRICT
c) Angabe von CASCADE

REFERENCES reftable [ ( refcolumn ) ] [ MATCH matchtype ] [ ON DELETE action ] [ ON UPDATE action ] (column constraint)
FOREIGN KEY ( column [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] [ MATCH matchtype ] [ ON DELETE action ] [ ON UPDATE action ] (table constraint)

These clauses specify a foreign key constraint, which requires that a group of one or more columns of the new table must only contain values that match values in the referenced column(s) of some row of the referenced table. If refcolumn is omitted, the primary key of the reftable is used. The referenced columns must be the columns of a unique or primary key constraint in the referenced table. Note that foreign key constraints may not be defined between temporary tables and permanent tables. 

A value inserted into the referencing column(s) is matched against the values of the referenced table and referenced columns using the given match type. There are three match types: MATCH FULL, MATCH PARTIAL, and MATCH SIMPLE, which is also the default. MATCH FULL will not allow one column of a multicolumn foreign key to be null unless all foreign key columns are null. MATCH SIMPLE allows some foreign key columns to be null while other parts of the foreign key are not null. MATCH PARTIAL is not yet implemented. 

In addition, when the data in the referenced columns is changed, certain actions are performed on the data in this table's columns. The ON DELETE clause specifies the action to perform when a referenced row in the referenced table is being deleted. Likewise, the ON UPDATE clause specifies the action to perform when a referenced column in the referenced table is being updated to a new value. If the row is updated, but the referenced column is not actually changed, no action is done. Referential actions other than the NO ACTION check cannot be deferred, even if the constraint is declared deferrable. There are the following possible actions for each clause: 

NO ACTION
Produce an error indicating that the deletion or update would create a foreign key constraint violation. If the constraint is deferred, this error will be produced at constraint check time if there still exist any referencing rows. This is the default action. 

RESTRICT
Produce an error indicating that the deletion or update would create a foreign key constraint violation. This is the same as NO ACTION except that the check is not deferrable. 

CASCADE
Delete any rows referencing the deleted row, or update the value of the referencing column to the new value of the referenced column, respectively. 

SET NULL
Set the referencing column(s) to null. 

SET DEFAULT
Set the referencing column(s) to their default values. 


If the referenced column(s) are changed frequently, it may be wise to add an index to the foreign key column so that referential actions associated with the foreign key column can be performed more efficiently.

gefunden in der doku: http://www.postgresql.org/docs/8.1/interactive/sql-createtable.html

:wink: damit sollte alles geklärt sein oder?

cu tb.