Are these two creation commands functionally identical?
I want to to know if these two are functionally equivalent. Is this:
Create table "users"
(
"userid" BigSerial NOT NULL,
"name" Varchar(20),
primary key ("userid")
) Without Oids;
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;
Is the above functionally identical to:
Create table "users"
(
"userid" BigSerial NOT NULL,
"name" Varchar(20),
primary key ("userid")
) Without Oids;
Create table "sales"
(
"saleid" BigSerial NOT NULL,
"userid" bigint references users(userid),
"parent_saleid" bigint references sales(saleid),
primary key ("saleid")
) Without Oids;
Using postgreSQL 8.1 if it matters, thanks.
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
On Aug 11, 5:21 am, gn...@hive.is (Ragnar) wrote:
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);
OK thank you. Besides the not null, are they otherwise identical?
The first statement is from a graphical db model tool I want to use,
the second is from my own handwritten sql. I want to see if it's
essentially the same. thanks.