Referential integrity using constant in foreign key

Started by Andrusabout 21 years ago8 messagesgeneral

eetasoft@online.ee

about 21 years ago

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.

Thomas F.O'Connell

tfo@sitening.com

about 21 years ago

In reply to: Andrus (#1)

Re: Referential integrity using constant in foreign key

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:

Show quoted text

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)

Andrus Moor

nospameetasoftnospam@online.ee

about 21 years ago

In reply to: Andrus (#1)

Re: Referential integrity using constant in foreign key

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 ?

Show quoted text

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.

Thomas F.O'Connell

tfo@sitening.com

about 21 years ago

In reply to: Andrus Moor (#3)

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:

Show quoted text

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.

Oisin Glynn

me@oisinglynn.com

about 21 years ago

In reply to: Andrus (#1)

Re: Referential integrity using constant in foreign key

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

Show quoted text

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

Florian Pflug

fgp@phlo.org

about 21 years ago

In reply to: Andrus Moor (#3)

Re: Referential integrity using constant in foreign key

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

Richard Huxton

dev@archonet.com

about 21 years ago

In reply to: Thomas F.O'Connell (#4)

Re: Referential integrity using constant in foreign key

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

Andrus

noeetasoftspam@online.ee

about 21 years ago

In reply to: Andrus (#1)

Re: Referential integrity using constant in foreign key

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.