Re: RI violated, bug or feature?

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.