Re: Self-referential records

Xi Shen <davidshen84(at)googlemail(dot)com> wrote:

> what if you insert other values like '1', '999'? will the insertion
> successful? if so, what's the difference between a deferred reference
> and no reference at all?

Nice question ;-)

Okay, recreate the table but without NOT NULL:

test=# CREATE TABLE refers ( id SERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, parent_id INTEGER , FOREIGN KEY (parent_id) REFERENCES refers(id) deferrable initially deferred);
NOTICE: CREATE TABLE will create implicit sequence "refers_id_seq" for serial column "refers.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "refers_pkey" for table "refers"
CREATE TABLE
Zeit: 63,477 ms
test=*# insert into refers (name, parent_id) values ('xxx',null);
INSERT 0 1
Zeit: 0,686 ms

I think, NULL is more sensible than inserting a 'random' value like 0.

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°