Pleas also note that the referenced column in the foreign table either needs to be the PRIMARY KEY or have a unique constraint on it or maybe it just requires an index on it -- I'm not sure but I discovered that if the column in the foreign table (containing the REFERENCED key...) is NOT the primary key column -- the REFERENCES a(x) will faill unless a.x is specified as 'UNIQUE' -- as in the following example:
This is according to the SQL specification, which doesn't like doubts.
Imagine rows (1, 99), (2, 99) in table a and row (3, 99) in table b. Which of the a-rows is now referenced and am I allowed to delete the other? There are good arguments either way, but if you require a UNIQUE on a.x, then this question will never come up.
Jan
create table a ( y integer not null primary key default nextval('nexta_seq'), x varchar not null UNIQUE
);
create table b (
z integer not null PRIMARY KEY default nextval('nextbz_seq'), x varchar NOT NULL REFERENCES a(x),
);
-- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== [EMAIL PROTECTED] #
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
