CODEWITHSUNDEEP
sqlpostgresql
soupdiver
soupdiver

Reputation: 3673

"No unique identifier for this row." in Postgres when trying to delete a row

I've got a simple table with the following scheme:

CREATE TABLE dateien(
    id int PRIMARY KEY UNIQUE DEFAULT NEXTVAL('dateien_id_seq'),
    name varchar(64),
    zeit timestamp DEFAULT now(),
    groesse float,
    dateityp_id int references dateitypen(id),
    benutzer_id int references benutzer(id)
);

When trying to delete a row via phppgadmin I always get the message: No unique identifier for this row.

Also via pure sql there is no effect after the delete statement. Can you tell me what the problem is?

Upvotes: 1

Views: 3834

Answers (2)

soupdiver
soupdiver

Reputation: 3673

Ah damn it... I found the mistake. I forgot to mention a trigger which is also active on this table. The trigger was:

CREATE TRIGGER update_quota BEFORE INSERT OR UPDATE OR DELETE
ON dateien
FOR EACH ROW
EXECUTE PROCEDURE f_update_quota();

But when deleting I returned something wrong. That's why it was not working.

Upvotes: 1

moskito-x
moskito-x

Reputation: 11968

I can not see how the referenced Tables are created.

dateityp_id int references dateitypen(id),
benutzer_id int references benutzer(id)

Therefore I can only guess ..., as an Example. Tables product,orders,dateien.

what if a product is removed after an order is created that references it? SQL allows you to specify that as well. Intuitively, we have a few options:

  • Disallow deleting a referenced product.
  • Delete the orders as well
  • Something else?

When someone wants to remove a product that is still referenced by an order (via dateien), we disallow it. If someone removes an order, the dateien are removed as well.

product_no integer REFERENCES products ON DELETE RESTRICT,
order_id integer REFERENCES orders ON DELETE CASCADE,

Restricting and cascading deletes are the two most common options. RESTRICT can also be written as NO ACTION and it's also the default if you do not specify anything. There are two other options for what should happen with the foreign key columns when a primary key is deleted: SET NULL and SET DEFAULT. Note that these do not excuse you from observing any constraints. For example, if an action specifies SET DEFAULT but the default value would not satisfy the foreign key, the deletion of the primary key will fail.

more Information : http://www.postgresql.org/docs/7.4/static/ddl-constraints.html

Upvotes: 0

Related Questions