Re: Referential integrity using constant in foreign key - Mailing list pgsql-general

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.