Lists: | pgsql-general |
---|
From: | Капралов Александр <alnkapa(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | How to make a non-removable row in a table? |
Date: | 2011-12-19 06:43:58 |
Message-ID: | CAJqqVEXwJUAb7nG7GHg+K93GvaRpM8ubgXkn5nOBYz5FOUSP6g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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');
From: | Thomas Markus <t(dot)markus(at)proventis(dot)net> |
---|---|
To: | PG-General Mailing List <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: How to make a non-removable row in a table? |
Date: | 2011-12-19 06:56:21 |
Message-ID: | 4EEEE015.8080501@proventis.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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');
>
From: | Капралов Александр <alnkapa(at)gmail(dot)com> |
---|---|
To: | Thomas Markus <t(dot)markus(at)proventis(dot)net> |
Cc: | PG-General Mailing List <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: How to make a non-removable row in a table? |
Date: | 2011-12-19 07:16:06 |
Message-ID: | CAJqqVEXWVWpBamihiWnXRmbBurHOj-C3w8KLy-XPM-_Uvjb4ug@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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(dot)markus(at)proventis(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
From: | Thomas Markus <t(dot)markus(at)proventis(dot)net> |
---|---|
To: | PG-General Mailing List <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: How to make a non-removable row in a table? |
Date: | 2011-12-19 08:03:11 |
Message-ID: | 4EEEEFBF.2020703@proventis.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | 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;
>
From: | Jasen Betts <jasen(at)xnet(dot)co(dot)nz> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: How to make a non-removable row in a table? |
Date: | 2012-01-25 12:12:39 |
Message-ID: | jforjn$vqm$5@reversiblemaps.ath.cx |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On 2011-12-19, Капралов Александр <alnkapa(at)gmail(dot)com> wrote:
> Hi all.
>
> How to make a non-removable row in a table?
reference it from another table.
--
⚂⚃ 100% natural