With classifier as you've defined it, if you want referential integrity in the info table, you could do this:
CREATE TABLE info ( code1 CHAR(10), code2 CHAR(10), FOREIGN KEY code1 REFERENCES classifier (category), FOREIGN KEY code2 REFERENCES classifier (category) );
But I'm not sure what you mean by "references to category 1". There is only a single category column in classifier, and referential integrity is not for ensuring that a column in one table contains only values of a single row.
Regardless, your syntax doesn't seem to reflect reality. Read the CREATE TABLE reference thoroughly.
http://www.postgresql.org/docs/8.0/static/sql-createtable.html
-tfo
-- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC
Strategic Open Source — Open Your i™
http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005
On Mar 25, 2005, at 10:22 AM, Andrus Moor wrote:
I need to create referential integrity constraints:
CREATE TABLE classifier ( category CHAR(1), code CHAR(10), PRIMARY KEY (category,code) );
-- code1 references to category 1, -- code2 references to category 2 from classifier table. CREATE TABLE info ( code1 CHAR(10), code2 CHAR(10), FOREIGN KEY ('1', category1) REFERENCES classifier, FOREIGN KEY ('2', category2) REFERENCES classifier );
Unfortunately, second CREATE TABLE causes error
ERROR: syntax error at or near "'1'" at character 171
Any idea how to implement referential integrity for info table ? It seems that this is not possible in Postgres.
Andrus.
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
