Thread: Referential integrity using constant in foreign key
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.
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 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to > majordomo@postgresql.org)
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.
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.
Is the goal to have code1 always equal 1 and code2 always to equal 2? If this is your goal and you are trying to ensure no-one enters anything other than a 1 in code1 or a 2 in code2 is a check constraint what you are after? I guess if the 2 columns code1 and code2 have fixed values 1 and 2 it seems like they might not be even needed? Not sure if that is what you are asking? Oisin ----- Original Message ----- From: "Thomas F.O'Connell" <tfo@sitening.com> To: "Andrus Moor" <nospameetasoftnospam@online.ee> Cc: <pgsql-general@postgresql.org> Sent: Monday, March 28, 2005 10:35 Subject: Re: [GENERAL] 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. > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings >
Andrus Moor wrote:
> 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 believe I get what you want to do - You basically have two (or more)
typs of codes, and thereforce your primary key on "classifier" is
(category, code). So far, this is basic database design, and your
solution is fine.
But now, you need to reference one type-1, and one type-2 code from the
"info" table. Now is is pretty non-standard (And, btw, it violates the
0-1-infinity rule, which says that you shouldn't introduce any abitrary
limits other than zero or one).
I believe you have two options. Either you keep your "dummy" columns -
which are not dummy columns at all, if you name them "category1" and
"category2". Then you just have two references to the "classifier"
table, each consiting of a "category" and a "code" - which is fine,
since this matches the primary key on "classifier".
Or you create a classifier_1 and a classifier_2 table, each containing
only the column "code". Then you can drop the "category1" and
"category2" fields from "info", and just point the foreign keys to the
correct table.
You can, optionally, create a view "classifer", that combiney both
classifier_? tables - e.g, do
create view classifier as
select '1'::char(1) as category, code from classifier_1
union
select '2'::char(1) as category, code from classifier_2 ;
greetings, Florian Pflug
Thomas F.O'Connell wrote: > 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. I know what he's trying to do, because I do it myself. And the short answer Andrus is "no, there is no shortcut". The typical usage is something like: CREATE TABLE contract (con_id int PRIMARY KEY, con_type varchar, con_date ...) CREATE TABLE purchase_details (con_id int, item_id int, qty int, ...) CREATE TABLE rental_details (con_id int, rental_period interval, ...) Now, you only want purchase_details to reference rows in contract where con_type="purchase". Likewise rental_details should only reference rows with con_type="rental". We can't reference a view, and we can't add a constant to the foreign-key definition. So, the options are: 1. Don't worry about it (not good design). 2. Add a "dummy" column to purchase_details which only contains the value "purchase" so we can reference the contract table (wasteful) 3. Write your own foreign-key triggers to handle this (a fair bit of work) 4. Eliminate the con_type column and determine it from what tables you join to. But that means you now need to write a custom constraint across all the xxx_details tables so that you don't get a mixed purchase/rental table. None of these are very attractive, but that's where we stand at the moment. HTH -- Richard Huxton Archonet Ltd
Florian, > Or you create a classifier_1 and a classifier_2 table, each containing > only the column "code". Then you can drop the "category1" and "category2" > fields from "info", and just point the foreign keys to the correct table. Thank you. I will probably go by this way. > You can, optionally, create a view "classifer", that combiney both > classifier_? tables - e.g, do > > create view classifier as > select '1'::char(1) as category, code from classifier_1 > union > select '2'::char(1) as category, code from classifier_2 ; I want to insert, update and delete using classifier view for max compatibility with existing shema from other DBMC which contains real classifier table. Which is the best way to make view changeable ? Is it possible to implement this using rules ? Is Postgres rule system best and reasonable solution for this? Andrus.