Re: TRIGGER TRUNCATE -- CASCADE or RESTRICT

On 03/06/15 08:40, Andreas Ulbrich wrote:
> On 02.06.2015 22:12, Melvin Davidson wrote:
>> Your problem is in your design.
>>
>> If you do it like this:
>>
>> CREATE TABLE A
>> (
>> p_col serial PRIMARY KEY,
>> acol integer
>> );
>>
>> CREATE TABLE B() INHERITS (A);
>>
>> INSERT INTO A(acol) VALUES (1);
>> INSERT INTO B(acol) VALUES (2);
>>
>> SELECT * FROM A;
>> SELECT * FROM B;
>>
>> Then the sequence (p_col) will be UNIQUE across all tables and can be
>> referenced.
>> No need for a key table.
> No, someone can do:
> INSERT INTO A VALUES (2,3);
> TABLE A;
> shows:
> p_col | acol
> -------+------
> 1 | 1
> 2 | 2
> 2 | 3
> p_col is not unique!
Curious, I tried to investigate, to get a better understanding and ran
into a problem...

$ psql
psql (9.4.1)
Type "help" for help.

gavin=> CREATE TABLE A
gavin-> (
gavin(> p_col serial PRIMARY KEY,
gavin(> acol integer
gavin(> );
CREATE TABLE
gavin=> CREATE TABLE B() INHERITS (A);
CREATE TABLE
gavin=> INSERT INTO A(acol) VALUES (1);
ERROR: column "acol" of relation "a" does not exist
LINE 1: INSERT INTO A(acol) VALUES (1);
^
gavin=> \d+ a
Table "public.a"
Column | Type |
Modifiers | Storage | Stats target | Description
--------+---------+---------------------------------------------------+---------+--------------+-------------
p_col | integer | not null default
nextval('a_p_col_seq'::regclass) | plain | |
acol | integer
| | plain
| |
Indexes:
"a_pkey" PRIMARY KEY, btree (p_col)
Child tables: b

gavin=> \d b
Table "public.b"
Column | Type | Modifiers
--------+---------+---------------------------------------------------
p_col | integer | not null default nextval('a_p_col_seq'::regclass)
acol | integer |
Inherits: a

gavin=>

[...]

Cheers,
Gavin