ADD CONSTRAINT ... FOREIGN KEY and custom data type. - Mailing list pgsql-hackers

For a project we are working on, I have created a custom postgresql data
type which is similar to MS SQL Server's uniqueidentifier data type. It uses
dynamic link library extension that calls the FreeDCE library to generate
GUIDs. Support for the data type and support functions is added to a
PostgreSQL database using the attached SQL script. The functions all seems
to work fine, including use of merge sorts and hash joins during SQL JOIN
statements when using the data type as part of a primary key.  However
adding foreign key constraints sometimes causes a problem.

I never have a problem adding a foreign key to a parent table with a
multi-part key as long as the child table is empty. Adding data to the child
entity afterwards seems to properly enforce RI.  However, if data exists in
the child entity, an RI check is performed on the existing data and this
check sometimes seems to break. As far as I can tell, the RI check in the
latter case seems to confuse the order the Key parts in either the Primary
Key or the Foreign Key. In the case of a multi-part key RI, it was
complaining that it couldn't perform a type conversion between the type of
two different key parts of the primary key.

So in a database with the following table definitions (OK I know it isn't
exactly great DB design to have 4 uniqueidentifiers in a PK, but please bear
with me) : 

--------------

CREATE TABLE Mo_Cvg_Rptd (      Emp_Grp_ID           uniqueidentifier NOT NULL,      Ben_Plan_ID
uniqueidentifierNOT NULL,      Grp_Rate_ID          uniqueidentifier NOT NULL,      Rate_Step_ID
uniqueidentifierNOT NULL,      Cvg_Yr_Mo            date NOT NULL,      Rptg_Session_ID      uniqueidentifier,
Mo_Cvg_Rptd_Sts_Cd  int2,      Mo_Except_Sts_Cd     int2,      Mo_Except_Desc       varchar(150),      Mdfy_Dt
   DATETIME NOT NULL DEFAULT date('now'),      PRIMARY KEY (Emp_Grp_ID, Ben_Plan_ID, Grp_Rate_ID,
Rate_Step_ID,Cvg_Yr_Mo)
 
);


CREATE TABLE Prior_Mo_Prd_Adjmt (      Emp_Grp_ID           uniqueidentifier NOT NULL,      Ben_Plan_ID
uniqueidentifierNOT NULL,      Grp_Rate_ID          uniqueidentifier NOT NULL,      Rate_Step_ID
uniqueidentifierNOT NULL,      Cvg_Yr_Mo            date NOT NULL,      Prior_Prd_Adjmt_Amt  int2,
Prior_Prd_Adjmt_Descvarchar(150),      Prior_Prd_Adjmt_Except_Sts_Cd int2,      Prior_Prd_Adjmt_Except_Desc
varchar(150),     PRIMARY KEY (Emp_Grp_ID, Ben_Plan_ID, Grp_Rate_ID,              Rate_Step_ID, Cvg_Yr_Mo)
 
);

ALTER TABLE Prior_Mo_Prd_Adjmt    ADD CONSTRAINT FK_PriorMoPrdAdjmt_MoCvgRptd FOREIGN KEY(Emp_Grp_ID,
                                        Ben_Plan_ID,
Grp_Rate_ID,                                                           Rate_Step_ID,
                       Cvg_Yr_Mo)   REFERENCES Mo_Cvg_Rptd;
 

--------------

If I want to change a column in Prior_Mo_Prd_Adjmt (with the aid of Erwin),
I have to drop the table and recreate it with a script similar to the
following:

**********************

CREATE TABLE prior_mo_prd_adjmtL25D4340000 (emp_grp_id uniqueidentifier,    ben_plan_id uniqueidentifier, grp_rate_id
uniqueidentifier, rate_step_id uniqueidentifier,    cvg_yr_mo date, prior_prd_adjmt_amt int4, prior_prd_adjmt_desc
varchar(150),prior_prd_adjmt_except_sts_cd int2,    prior_prd_adjmt_except_desc varchar(150));
 


INSERT INTO prior_mo_prd_adjmtL25D4340000 (emp_grp_id, ben_plan_id,
grp_rate_id,   rate_step_id, cvg_yr_mo, prior_prd_adjmt_amt, prior_prd_adjmt_desc,   prior_prd_adjmt_except_sts_cd,
prior_prd_adjmt_except_desc)  SELECT emp_grp_id, ben_plan_id, grp_rate_id,       rate_step_id, cvg_yr_mo,
prior_prd_adjmt_amt,prior_prd_adjmt_desc,       prior_prd_adjmt_except_sts_cd, prior_prd_adjmt_except_desc   FROM
prior_mo_prd_adjmt;


DROP TABLE prior_mo_prd_adjmt;


CREATE TABLE Prior_Mo_Prd_Adjmt (      Emp_Grp_ID           uniqueidentifier NOT NULL,      Ben_Plan_ID
uniqueidentifierNOT NULL,      Grp_Rate_ID          uniqueidentifier NOT NULL,      Rate_Step_ID
uniqueidentifierNOT NULL,      Cvg_Yr_Mo            date NOT NULL,      Prior_Prd_Adjmt_Amt  numeric(9,2),
Prior_Prd_Adjmt_Descvarchar(150),      Prior_Prd_Adjmt_Except_Sts_Cd int2,      Prior_Prd_Adjmt_Except_Desc
varchar(150),     PRIMARY KEY (Emp_Grp_ID, Ben_Plan_ID, Grp_Rate_ID,              Rate_Step_ID, Cvg_Yr_Mo)
 
);

CREATE UNIQUE INDEX IDX_Prior_Mo_Prd_Adjmt_PK ON Prior_Mo_Prd_Adjmt
(      Emp_Grp_ID,      Ben_Plan_ID,      Grp_Rate_ID,      Rate_Step_ID,      Cvg_Yr_Mo
);

ALTER TABLE Prior_Mo_Prd_Adjmt    ADD CONSTRAINT FK_PriorMoPrdAdjmt_MoCvgRptd FOREIGN KEY(Emp_Grp_ID,
                                        Ben_Plan_ID,
Grp_Rate_ID,                                                           Rate_Step_ID,
                       Cvg_Yr_Mo)   REFERENCES Mo_Cvg_Rptd;
 


INSERT INTO Prior_Mo_Prd_Adjmt (Emp_Grp_ID, Ben_Plan_ID, Grp_Rate_ID,    Rate_Step_ID, Cvg_Yr_Mo, Prior_Prd_Adjmt_Amt,
Prior_Prd_Adjmt_Desc,   Prior_Prd_Adjmt_Except_Sts_Cd, Prior_Prd_Adjmt_Except_Desc) SELECT    emp_grp_id, ben_plan_id,
grp_rate_id,rate_step_id, cvg_yr_mo,    prior_prd_adjmt_amt, prior_prd_adjmt_desc,
 
prior_prd_adjmt_except_sts_cd,    prior_prd_adjmt_except_desc FROM prior_mo_prd_adjmtL25D4340000;


DROP TABLE prior_mo_prd_adjmtL25D4340000;

**********************

Now the interesting thing is that if the ALTER TABLE statement is placed
after the INSERT I get the following error:

psql:upgradetohbtweb205.sql:61: NOTICE:  ALTER TABLE ... ADD CONSTRAINT will
create implicit trigger(s) for FOREIGN KEY check(s)
psql:upgradetohbtweb205.sql:61: ERROR:  Unable to identify an operator '='
for types 'uniqueidentifier' and 'date'       You will have to retype this query using an explicit cast

But I get no error with the ALTER before the INSERT!

In this case I can work around the bug, but I can't in the case of a 3+
table PK chain grandparent->parent->child where I don't want to have to drop
the child table (and anything referring to it) to be able to change the
parent table. (The child will have a FK to the parent which will break with
the above error when I try to recreate it on the new incarnation of the
parent.)

I haven't seen any references to similar foreign key problems in the lists,
which is why I am not sure if the problem is a function of the extended
type. However since the behaviour is different - for the same data -
depending on whether the RI check is made when creating the FOREIGN KEY vs.
when adding data after the key is entered, it seems reasonable to suspect a
bug in the first case.

Any chance this might be addressed for 7.1?

Thank you,

Paul-Andre Panon

--
Paul-AndrePanon@SierraSystems.com
<<PGUniqID.sql>>