Обсуждение: How to make a non-removable row in a table?
Hi all. How to make a non-removable row in a table? In my case, I should not accidentally delete a row with id = 0. CREATE TABLE profile ( id integer NOT NULL, name character varying(265) NOT NULL ); CREATE SEQUENCE profile_id_seq START WITH 1 INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1; ALTER TABLE profile ALTER COLUMN id SET DEFAULT nextval('profile_id_seq'::regclass); ALTER TABLE ONLY profile ADD CONSTRAINT profile_pkey PRIMARY KEY (id); INSERT INTO profile VALUES (0,'non-removable Profile');
Hi, create a delete trigger that raises an exception Thomas Am 19.12.2011 07:43, schrieb Капралов Александр: > Hi all. > > How to make a non-removable row in a table? > > In my case, I should not accidentally delete a row with id = 0. > > CREATE TABLE profile ( > > id integer NOT NULL, > > name character varying(265) NOT NULL > > ); > > CREATE SEQUENCE profile_id_seq > START WITH 1 > INCREMENT BY 1 > NO MAXVALUE > NO MINVALUE > CACHE 1; > > ALTER TABLE profile ALTER COLUMN id SET DEFAULT > nextval('profile_id_seq'::regclass); > > ALTER TABLE ONLY profile ADD CONSTRAINT profile_pkey PRIMARY KEY (id); > > INSERT INTO profile VALUES (0,'non-removable Profile'); >
I found a simple solution, but i don't know how to add raises an exception here. create rule protect_profile_id0_update as on update to web.profile where old.id = 0 do instead nothing; create rule protect_profile_id0_delete as on delete to web.profile where old.id = 0 do instead nothing; 2011/12/19 Thomas Markus <t.markus@proventis.net>: > Hi, > > create a delete trigger that raises an exception > > Thomas > > > Am 19.12.2011 07:43, schrieb Капралов Александр: > >> Hi all. >> >> How to make a non-removable row in a table? >> >> In my case, I should not accidentally delete a row with id = 0. >> >> CREATE TABLE profile ( >> >> id integer NOT NULL, >> >> name character varying(265) NOT NULL >> >> ); >> >> CREATE SEQUENCE profile_id_seq >> START WITH 1 >> INCREMENT BY 1 >> NO MAXVALUE >> NO MINVALUE >> CACHE 1; >> >> ALTER TABLE profile ALTER COLUMN id SET DEFAULT >> nextval('profile_id_seq'::regclass); >> >> ALTER TABLE ONLY profile ADD CONSTRAINT profile_pkey PRIMARY KEY (id); >> >> INSERT INTO profile VALUES (0,'non-removable Profile'); >> > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
Hi, simple violate a contraint. my test: drop table if exists x; create temp table x ( id int not null primary key, name text ); -- check against not null create rule test_rule as on delete to x where old.id=1 do instead update x set id=null; insert into x values( 1,'a'),(2,'b'); select * from x; -- fails delete from x; delete from x where id!=1; select * from x; regards Thomas Am 19.12.2011 08:16, schrieb Капралов Александр: > I found a simple solution, but i don't know how to add raises an exception here. > > create rule protect_profile_id0_update as on update to web.profile > where old.id = 0 do instead nothing; > create rule protect_profile_id0_delete as on delete to web.profile > where old.id = 0 do instead nothing; >
On 2011-12-19, Капралов Александр <alnkapa@gmail.com> wrote: > Hi all. > > How to make a non-removable row in a table? reference it from another table. -- ⚂⚃ 100% natural