Re: Referential integrity using constant in foreign key

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.