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