How to Prevent Deletion of the First Row in Table (Postgresql)

How to prevent deletion of the first row in table (PostgreSQL)?

The best way I see to accomplish this is by creating a delete trigger on this table. Basically, you'll have to write a stored procedure to make sure that this 'default' category will always exist, and then enforce it using a trigger ON DELETE event on this table. A good way to do this is create a per-row trigger that will guarantee that on DELETE events the 'default' category row will never be deleted.

Please check out PostgreSQL's documentation about triggers and stored procedures:

http://www.postgresql.org/docs/8.3/interactive/trigger-definition.html

http://www.postgresql.org/docs/8.3/interactive/plpgsql.html

There's also valuable examples in this wiki:

http://wiki.postgresql.org/wiki/A_Brief_Real-world_Trigger_Example

Disable DELETE on table in PostgreSQL?

As I understand a rule would generate additional queries - so a rule could not suppress the original query.

Not really - it could be an INSTEAD rule:

 CREATE RULE shoe_del_protect AS ON DELETE TO shoe DO INSTEAD NOTHING;

(an example on that same page of the manual).

Another way is to REVOKE delete privileges on the table in question and to create stored procedure(s) for deleting... and updating and inserting also probably.

Prevent deletion of table records for one user and allow deletion for another in postgresql

A trigger executed before delete should return old.

CREATE OR REPLACE FUNCTION prevent_deletion() 
RETURNS trigger AS $$
BEGIN
    IF current_user != 'user1' THEN
        RAISE EXCEPTION 'You cannot delete records from this table!';
    END IF;  
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

query for my first row admin for never delete

Why don't you use following simply mysql query...

DELETE FROM users_tbl WHERE id = 2 AND username != 'admin';

It would never delete admin row.

MySQL doesn't prevent deletion of rows where primary key is associated with another table

Here is the example for MYSQL. You need add the foreign key with update and delete constrains. You can also read through the documents for more details. Foreign Key

CREATE TABLE account (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(30)
);

CREATE TABLE transaction (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    account_id BIGINT,
    FOREIGN KEY (account_id) REFERENCES account(id) ON UPDATE CASCADE ON DELETE RESTRICT
);

INSERT INTO account (name) VALUES ('john');
INSERT INTO transaction (account_id) VALUES (1);

DELETE FROM account where id = 1;

-- 18:32:14 DELETE FROM account where id = 1    Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`test`.`transaction`, CONSTRAINT `transaction_ibfk_1` FOREIGN KEY (`account_id`) REFERENCES `account` (`id`) ON UPDATE CASCADE) 0.039 sec

---

Related Topics