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

Andrus, it's still not clear to me that you're understanding the role
of referential integrity in database design. It exists to guarantee
that the values in a column in a given table correspond exactly to the
values in a column in another table on a per-row basis. It does not
exist to guarantee that all values in a given column will have a
specific value.

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.