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 Капралов Александр:
Show quoted text
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>:
Show quoted text
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 Капралов Александр:
Show quoted text
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;
