The following bug has been logged online:
Bug reference: 1006
Logged by: Majolee InfoTech
Email address: [EMAIL PROTECTED]
PostgreSQL version: 7.4
Operating system: Redhat 9.0
Description: information schema constraint information.
Details:
Hello,
as per the documentation of information schema (constraint_column_usage) view should
return exact column names for a constraint created.
Currently this view has a bug for foreign key constraints created for a table for more
than one times. It shows first inserted column name for all of the following foreign
keys defined for same table.
#####################################################
CREATE TABLE public.test
(
fld1 varchar(25) NOT NULL,
fld2 varchar(25),
fld3 varchar(25),
CONSTRAINT pk1 PRIMARY KEY (fld1)
) WITH OIDS;
CREATE TABLE public.test2
(
pk2 int8 NOT NULL,
fk1 varchar(25),
CONSTRAINT pk22 PRIMARY KEY (pk2),
CONSTRAINT fk11 FOREIGN KEY (fk1) REFERENCES public.test (fld1) ON UPDATE RESTRICT
ON DELETE RESTRICT
) WITH OIDS;
CREATE TABLE public.test3
(
fld_1 varchar(25) NOT NULL,
fld_2 varchar(25) NOT NULL,
fld_3 varchar(25) NOT NULL,
CONSTRAINT pk3 PRIMARY KEY (fld_1),
CONSTRAINT fk3_1 FOREIGN KEY (fld_2) REFERENCES public.test (fld1) ON UPDATE
RESTRICT ON DELETE RESTRICT,
CONSTRAINT fk3_2 FOREIGN KEY (fld_3) REFERENCES public.test2 (pk2) ON UPDATE
RESTRICT ON DELETE RESTRICT
) WITH OIDS;
#####################################################
This on querying
#####################################################
select * from information_schema.constraint_column_usage
#####################################################
gives following output
#####################################################
table_catalog | table_schema | table_name | column_name | constraint_catalog |
constraint_schema | constraint_name
---------------+--------------+------------+-------------+--------------------+-------------------+-----------------
ERP | public | test | fld1 | ERP | public
| pk1
ERP | public | test2 | pk2 | ERP | public
| pk22
ERP | public | test2 | pk2 | ERP | public
| fk11
ERP | public | test3 | fld_1 | ERP | public
| pk3
ERP | public | test3 | fld_1 | ERP | public
| fk3_1
ERP | public | test3 | fld_1 | ERP | public
| fk3_2
#####################################################
Which should show (Changes displayed within *CHANGE*)
#####################################################
table_catalog | table_schema | table_name | column_name | constraint_catalog |
constraint_schema | constraint_name
---------------+--------------+------------+-------------+--------------------+-------------------+-----------------
ERP | public | test | fld1 | ERP | public
| pk1
ERP | public | test2 | pk2 | ERP | public
| pk22
ERP | public | test2 | *fk1* | ERP |
public | fk11
ERP | public | test3 | fld_1 | ERP | public
| pk3
ERP | public | test3 | *fld_2* | ERP |
public | fk3_1
ERP | public | test3 | *fld_3* | ERP |
public | fk3_2
#####################################################
Please update us on the same.
Thanks.....
Majolee InfoTech
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
