Referential integrity never dictates the need for "dummy" columns. If you have a column that you need to refer to a column in another table so strongly that you want the values always to be in sync, you create a foreign key, establishing referential integrity between a column (or columns) in the table with the foreign key and a column in another table (usually a primary key).
I don't understand what you're trying to accomplish well enough to be able to make a specific recommendation based on your examples that suits your needs.
-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 1:39 PM, Andrus Moor wrote:
Thomas,
thank you for reply. There was a typo in my code. Second table should be
CREATE TABLE info ( code1 CHAR(10), code2 CHAR(10), FOREIGN KEY ('1', code1) REFERENCES classifier, FOREIGN KEY ('2', code2) REFERENCES classifier );
I try to explain my problem more precicely.
I can implement the referential integrity which I need in the following way:
CREATE TABLE classifier ( category CHAR(1), code CHAR(10), PRIMARY KEY (category,code) );
CREATE TABLE info ( code1 CHAR(10), code2 CHAR(10), constant1 CHAR default '1', constant2 CHAR default '2', FOREIGN KEY (constant1, code1) REFERENCES classifier, FOREIGN KEY (constant2, code2) REFERENCES classifier );
This implementation requires 2 additional columns (constant1 and constant2)
which have always same values, '1' and '2' respectively, in all info table
rows.
I created those dummy columns since Postgres does not allow to write REFERENCES clause like
CREATE TABLE info ( code1 CHAR(10), code2 CHAR(10), FOREIGN KEY ('1', code1) REFERENCES classifier, FOREIGN KEY ('2', code2) REFERENCES classifier );
Is it possible to implement referential integrity without adding additional
dummy columns to info table ?
It's somewhat unclear what you're attempting to do, here, but I'll give a
shot at interpreting. Referential integrity lets you guarantee that values
in a column or columns exist in a column or columns in another table.
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 7: don't forget to increase your free space map settings
