Thread: 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