Re: Are these two creation commands functionally identical?

On fim, 2007-08-09 at 20:55 +0000, dterrors@hotmail.com wrote:
> I want to to know if these two are functionally equivalent.  Is this:
>
> Create table "sales"
> (
>     "saleid" BigSerial NOT NULL,
>     "userid" Bigint NOT NULL,
>     "parent_saleid" Bigint NOT NULL,
>  primary key ("saleid")
> ) Without Oids;
> Alter table "sales" add  foreign key ("userid") references
> "users" ("userid") on update restrict on delete restrict;
> Alter table "sales" add  foreign key ("parent_saleid") references
> "sales" ("saleid") on update restrict on delete restrict;
this constraint seems a bit strange to me. are you going
to special-case the first insert into this table?


> Is the above functionally identical to:
>
> Create table "sales"
> (
>     "saleid" BigSerial NOT NULL,
>     "userid" bigint references users(userid),
>     "parent_saleid" bigint references sales(saleid),
>  primary key ("saleid")
> ) Without Oids;

no these 2 are not fuctionally identical, because the second one
does not have a NOT NULL constraint on the foreign keys,
allowing you to insert:
INSERT INTO sales (saleid,userid,parent_saleid)
    VALUES (100,null,100);


gnari