Thread: RI violated, bug or feature?

Hi,

my 7.0.2 runs no stable, all tests except one numerical (least
signifcant digit was off by one) ran successfull. No I did play
with RI and discovered the following:

Lets create two tables:

    test_ri=> create table publisher (name char(20));
    CREATE

    test_ri=> create index publ on publisher (name);
    CREATE

    test_ri=> create table books (title char(32), publisher char(20)
references publisher(name));
    NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
    CREATE

Now let's insert some data:

    test_ri=> insert into publisher (name) values ('prentice');
    INSERT 35900 1
    test_ri=> insert into publisher (name) values ('addison');
    INSERT 35901 1
    test_ri=> insert into publisher (name) values ('springer');
    INSERT 35902 1

and at least

    test_ri=> insert into books (title) values ('violated');
    INSERT 35903 1

Hey, I have now a row in books, which has no corresponding
publisher entry. Let's do another test:

    test_ri=> insert into books (title, publisher) values ('dummy', 'x');
    ERROR:  <unnamed> referential integrity violation - key referenced from books not found in publisher

Ok, that's what I expected.

    test_ri=>  insert into books (title, publisher) values ('XML handbook', 'prentice');
    INSERT 35905 1

That's ok too.

If I create table books again and add a "DEFAULT ''" clause to
column publisher then inserting rows with empty field publisher
is prohibited.

Is this a bug or is it a feature?

Martin Jacobs

100.179370@germanynet.de (Martin Jacobs) writes:
>     test_ri=> insert into books (title) values ('violated');
>     INSERT 35903 1

> Hey, I have now a row in books, which has no corresponding
> publisher entry. Let's do another test:

If you don't want to allow NULLs in a referencing column, add a
NOT NULL constraint to it.  Otherwise they're allowed.

            regards, tom lane

On Sat, 14 Oct 2000, Martin Jacobs wrote:

> Hi,
>
> my 7.0.2 runs no stable, all tests except one numerical (least
> signifcant digit was off by one) ran successfull. No I did play
> with RI and discovered the following:
>
> Lets create two tables:
>
>     test_ri=> create table publisher (name char(20));
>     CREATE
>
>     test_ri=> create index publ on publisher (name);
>     CREATE
>
>     test_ri=> create table books (title char(32), publisher char(20)
> references publisher(name));
>     NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
>     CREATE
>
> Now let's insert some data:
>
>     test_ri=> insert into publisher (name) values ('prentice');
>     INSERT 35900 1
>     test_ri=> insert into publisher (name) values ('addison');
>     INSERT 35901 1
>     test_ri=> insert into publisher (name) values ('springer');
>     INSERT 35902 1
>
> and at least
>
>     test_ri=> insert into books (title) values ('violated');
>     INSERT 35903 1
>
> Hey, I have now a row in books, which has no corresponding
> publisher entry. Let's do another test:
>

True, but it is NULL which is explicitly allowed by the spec.
(If no <match type> was specified then, for each row R1 of the
referencing table, either at least one of the values of the
referencing columns in R1 shall be a null value, or the value of
each referencing column in R1 shall be equal to the value of the
corresponding referenced column in some row of the referenced
table.)

So, that's not a violation of the constraint.  If you want to
disallow nulls, you have to explicitly make the column
NOT NULL.