Re: [GENERAL] 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. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
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
Re: [GENERAL] 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 [EMAIL PROTECTED]
To: Andrus Moor [EMAIL PROTECTED]
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
Re: [GENERAL] 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
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] 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 ?
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 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[GENERAL] 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.
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] 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:
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
[EMAIL PROTECTED])
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
