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