pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Problem with subquery in CHECK constraint.
7+ messages / 4 participants
[nested] [flat]

* Problem with subquery in CHECK constraint.
@ 2000-06-07 15:24  Niall Smart <niall.smart@ebeon.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Niall Smart @ 2000-06-07 15:24 UTC (permalink / raw)
  To: pgsql-sql

Hi,

I'm using a general lookup table defined as following
to avoid a proliferation of lookup tables:

CREATE TABLE XREF
(
        XREF_GROUP      VARCHAR(12)     NOT NULL,
        XREF_CD         VARCHAR(8)      NOT NULL,
        XREF_VALUE      VARCHAR(128),

        PRIMARY KEY (XREF_GROUP, XREF_CD)
);

INSERT INTO XREF VALUES ('CUST_TYPE', 'EXP', 'Expired');
INSERT INTO XREF VALUES ('CUST_TYPE', 'ACTIV', 'Active');
INSERT INTO XREF VALUES ('CUST_TYPE', 'POT', 'Potential');

I'm trying to define a check constraint to validate
lookup codes used, for example:

CREATE TABLE CUST
(
        CUST_ID         INTEGER NOT NULL,
        NAME            VARCHAR(64) NOT NULL,
        TYPE_CD         VARCHAR(8) NOT NULL,
        
        CONSTRAINT CUST_PK PRIMARY KEY (CUST_ID),
        CONSTRAINT TYPE_CD_OK CHECK ( EXISTS (SELECT 1 FROM XREF WHERE
                                        XREF_GROUP = 'CUST_TYPE' AND XREF_CD = TYPE_CD) )
);

However when trying to insert into CUST I get the following error:

        ERROR:  ExecEvalExpr: unknown expression type 108

Is this a bug in PostGreSQL?  I can work around it by
defining a function and using it in the CHECK constraint
for now.  See the appended test file for example SQL.

By the way, there was a massive performance difference in using:

        SELECT $2 IS NULL OR EXISTS (SELECT 1 FROM XREF WHERE XREF_GROUP = $1 AND XREF_CD = $2)

versus the slower:

        SELECT $2 IN (SELECT XREF_CD FROM XREF WHERE XREF_GROUP = $1)

for validation, is this to be expected?

Here's the test case:

DROP TABLE XREF;

CREATE TABLE XREF
(
        XREF_GROUP      VARCHAR(12)     NOT NULL,
        XREF_CD         VARCHAR(8)      NOT NULL,
        XREF_VALUE      VARCHAR(128),

        PRIMARY KEY (XREF_GROUP, XREF_CD)
);

INSERT INTO XREF VALUES ('CUST_TYPE', 'EXP', 'Expired'); 
INSERT INTO XREF VALUES ('CUST_TYPE', 'ACTIV', 'Active'); 
INSERT INTO XREF VALUES ('CUST_TYPE', 'POT', 'Potential'); 


DROP FUNCTION VALID_XREF(VARCHAR(12), VARCHAR(8));

CREATE FUNCTION VALID_XREF(VARCHAR(12), VARCHAR(8)) RETURNS BOOLEAN AS
        'SELECT $2 IS NULL OR EXISTS (SELECT 1 FROM XREF WHERE XREF_GROUP = $1 AND XREF_CD = $2)'
LANGUAGE 'SQL';

--
-- Much slower version:
--
--      'SELECT $2 IN (SELECT XREF_CD FROM XREF WHERE XREF_GROUP = $1)'
--


DROP TABLE CUST;

CREATE TABLE CUST
(
        CUST_ID         INTEGER NOT NULL,
        NAME            VARCHAR(64) NOT NULL,
        TYPE_CD         VARCHAR(8) NOT NULL,

        CONSTRAINT CUST_PK PRIMARY KEY (CUST_ID),
        CONSTRAINT TYPE_CD_OK CHECK ( EXISTS (SELECT 1 FROM XREF WHERE
                                                XREF_GROUP = 'CUST_TYPE' AND XREF_CD = TYPE_CD) )
);

INSERT INTO CUST VALUES (1, 'Joe Bloggs', 'POT');


DROP TABLE CUST;

CREATE TABLE CUST
(
        CUST_ID         INTEGER NOT NULL,
        NAME            VARCHAR(64) NOT NULL,
        TYPE_CD         VARCHAR(8) NOT NULL,

        CONSTRAINT CUST_PK PRIMARY KEY (CUST_ID),
        CONSTRAINT TYPE_CD_OK CHECK (VALID_XREF('CUST_TYPE', TYPE_CD))
);

INSERT INTO CUST VALUES (1, 'Joe Bloggs', 'ACTIV');
INSERT INTO CUST VALUES (2, 'Jim Smith', 'foo');



--
Niall Smart

email:  niall.smart@ebeon.com
phone:  (087) 8052390



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Problem with subquery in CHECK constraint.
@ 2000-06-07 15:48  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Niall Smart <niall.smart@ebeon.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Tom Lane @ 2000-06-07 15:48 UTC (permalink / raw)
  To: Niall Smart <niall.smart@ebeon.com>; +Cc: pgsql-sql

Niall Smart <niall.smart@ebeon.com> writes:
> However when trying to insert into CUST I get the following error:
>         ERROR:  ExecEvalExpr: unknown expression type 108
> Is this a bug in PostGreSQL?

Yup.  Looks like the subquery is not getting expanded into a subplan.
Could be a little messy to fix, unless we redo the planning on every
execution of the check constraint ... which doesn't sound appetizing.

For now I'd suggest putting the subquery into an SQL or plpgsql function
that you invoke from the constraint expression.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* RE: Problem with subquery in CHECK constraint.
@ 2000-06-08 00:24  Hiroshi Inoue <Inoue@tpf.co.jp>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 7+ messages in thread

From: Hiroshi Inoue @ 2000-06-08 00:24 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; Niall Smart <niall.smart@ebeon.com>; +Cc: pgsql-sql

> -----Original Message-----
> From: pgsql-sql-owner@hub.org [mailto:pgsql-sql-owner@hub.org]On Behalf
> Of Tom Lane
> 
> Niall Smart <niall.smart@ebeon.com> writes:
> > However when trying to insert into CUST I get the following error:
> >         ERROR:  ExecEvalExpr: unknown expression type 108
> > Is this a bug in PostGreSQL?
> 
> Yup.  Looks like the subquery is not getting expanded into a subplan.
> Could be a little messy to fix, unless we redo the planning on every
> execution of the check constraint ... which doesn't sound appetizing.
>

There seems to be more serious problems.
1) The constraint is not only for the defined table but also for referenced
    tables in the subquery.
2) There should be some standard lock mechanism for the range restricted
    by the subquery. 

I'm suspicious that we should/could implement constraints other than 
column constraints.   

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Problem with subquery in CHECK constraint.
@ 2000-06-08 11:56  Niall Smart <niall.smart@ebeon.com>
  parent: Hiroshi Inoue <Inoue@tpf.co.jp>
  0 siblings, 2 replies; 7+ messages in thread

From: Niall Smart @ 2000-06-08 11:56 UTC (permalink / raw)
  To: Hiroshi Inoue <Inoue@tpf.co.jp>; +Cc: pgsql-sql


CONSTRAINT TYPE_CD_OK CHECK (
	EXISTS (SELECT 1 FROM XREF WHERE 
			XREF_GROUP = 'CUST_TYPE' AND
			XREF_CD = TYPE_CD)
)


> There seems to be more serious problems.
> 1) The constraint is not only for the defined table but also for referenced
>     tables in the subquery.

I don't understand what you mean -- the constraint only
constrains 1 column in one table...

> 2) There should be some standard lock mechanism for the range restricted
>     by the subquery.
> 
> I'm suspicious that we should/could implement constraints other than
> column constraints.

Again, I don't fully understand what you're saying; but I
have successfully implemented the constraint using a user
defined function.

Niall



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* RE: Problem with subquery in CHECK constraint.
@ 2000-06-08 18:01  Hiroshi Inoue <Inoue@tpf.co.jp>
  parent: Niall Smart <niall.smart@ebeon.com>
  1 sibling, 1 reply; 7+ messages in thread

From: Hiroshi Inoue @ 2000-06-08 18:01 UTC (permalink / raw)
  To: Niall Smart <niall.smart@ebeon.com>; +Cc: pgsql-sql

> -----Original Message-----
> From: pgsql-sql-owner@hub.org [mailto:pgsql-sql-owner@hub.org]On 
> Behalf Of Niall Smart
> 
> 
> CONSTRAINT TYPE_CD_OK CHECK (
> 	EXISTS (SELECT 1 FROM XREF WHERE 
> 			XREF_GROUP = 'CUST_TYPE' AND
> 			XREF_CD = TYPE_CD)
> )
> 
> 
> > There seems to be more serious problems.
> > 1) The constraint is not only for the defined table but also 
> for referenced
> >     tables in the subquery.
> 
> I don't understand what you mean -- the constraint only
> constrains 1 column in one table...
>

Doesn't the constraint mean that
	for any row in table CUST,there *always* exist some rows in
	the table XREF such that satisfies XREF_GROUP='CUST_TYPE'
	AND XREF_CD=TYPE_CD ?
If all such rows are deleted from the table XREF,above condition
isn't satisfied any longer. So isn't the constraint for the table XREF
either ?
 
Regards.

Hiroshi Inoue
Inoue@tpf.co.jp



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Problem with subquery in CHECK constraint.
@ 2000-06-08 18:15  Stephan Szabo <sszabo@kick.com>
  parent: Niall Smart <niall.smart@ebeon.com>
  1 sibling, 0 replies; 7+ messages in thread

From: Stephan Szabo @ 2000-06-08 18:15 UTC (permalink / raw)
  To: Niall Smart <niall.smart@ebeon.com>; +Cc: pgsql-sql

> CONSTRAINT TYPE_CD_OK CHECK (
> EXISTS (SELECT 1 FROM XREF WHERE
> XREF_GROUP = 'CUST_TYPE' AND
> XREF_CD = TYPE_CD)
> )
>
>
> > There seems to be more serious problems.
> > 1) The constraint is not only for the defined table but also for
referenced
> >     tables in the subquery.
>
> I don't understand what you mean -- the constraint only
> constrains 1 column in one table...

Think of this sequence:

insert into xref (xref_group, xref_cd) values ('CUST_TYPE', '1');
insert into xref (xref_group, xref_cd) values ('CUST_TYPE', '2');
insert into xref (xref_group, xref_cd) values ('CUST_TYPE', '3');
insert into cust (cust_id, name, type_cd) values (1, 'a', '1');
insert into cust (cust_id, name, type_cd) values (2, 'b', '2');
delete from xref where xref_cd='1';

I believe that technically the delete should fail because it breaks the
type_cd_ok constraint, but right now, we wouldn't be even checking
that constraint to notice that we're breaking it.






^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Problem with subquery in CHECK constraint.
@ 2000-06-09 11:08  Niall Smart <niall.smart@ebeon.com>
  parent: Hiroshi Inoue <Inoue@tpf.co.jp>
  0 siblings, 0 replies; 7+ messages in thread

From: Niall Smart @ 2000-06-09 11:08 UTC (permalink / raw)
  To: Hiroshi Inoue <Inoue@tpf.co.jp>; +Cc: pgsql-sql

Hiroshi Inoue wrote:
> > From: pgsql-sql-owner@hub.org [mailto:pgsql-sql-owner@hub.org]On
> > Behalf Of Niall Smart
> >
> > CONSTRAINT TYPE_CD_OK CHECK (
> >       EXISTS (SELECT 1 FROM XREF WHERE
> >                       XREF_GROUP = 'CUST_TYPE' AND
> >                       XREF_CD = TYPE_CD)
> > )
> >
> >
> > > There seems to be more serious problems.
> > > 1) The constraint is not only for the defined table but also
> > for referenced
> > >     tables in the subquery.
> >
> > I don't understand what you mean -- the constraint only
> > constrains 1 column in one table...
> 
> Doesn't the constraint mean that
>         for any row in table CUST,there *always* exist some rows in
>         the table XREF such that satisfies XREF_GROUP='CUST_TYPE'
>         AND XREF_CD=TYPE_CD ?
>
> If all such rows are deleted from the table XREF,above condition
> isn't satisfied any longer. So isn't the constraint for the table XREF
> either ?

Ah, I see what you mean now.  Well, thats an interesting point,
as I originally tried to define the constraint using the
foreign key syntax.  Perhaps a better way to support this
functionality is to allow constants in the source columns
of a foreign key constraint, for example:

	CONSTRAINT TYPE_CD_OK
		FOREIGN KEY ('CUST_TYPE' AS XREF_GROUP, XREF_CD)
		REFERENCES XREF

However I would submit that constraint clauses are
effectively before-insert validation triggers and that
it is up to the database designer to use more robust
methods when they wish to enforce referential integrity.

Note that it is possible to define a CHECK constraint using
a function, in which case it is impossible to determine if
the function is expressing a relational integrity constraint.


Niall



^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2000-06-09 11:08 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-06-07 15:24 Problem with subquery in CHECK constraint. Niall Smart <niall.smart@ebeon.com>
2000-06-07 15:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-06-08 00:24   ` Hiroshi Inoue <Inoue@tpf.co.jp>
2000-06-08 11:56     ` Niall Smart <niall.smart@ebeon.com>
2000-06-08 18:01       ` Hiroshi Inoue <Inoue@tpf.co.jp>
2000-06-09 11:08         ` Niall Smart <niall.smart@ebeon.com>
2000-06-08 18:15       ` Stephan Szabo <sszabo@kick.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox