pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Niall Smart <niall.smart@ebeon.com>
To: pgsql-sql@postgresql.org
Subject: Problem with subquery in CHECK constraint.
Date: Wed, 07 Jun 2000 16:24:49 +0100
Message-ID: <393E6940.E3CDD1BD@ebeon.com> (raw)

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



view thread (7+ messages)  latest in thread

Message-ID: <393E6940.E3CDD1BD@ebeon.com>
Permalink:  ../393E6940.E3CDD1BD@ebeon.com/
Also on:    postgresql.org/message-id/393E6940.E3CDD1BD@ebeon.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: niall.smart@ebeon.com
  Subject: Re: Problem with subquery in CHECK constraint.
  In-Reply-To: <393E6940.E3CDD1BD@ebeon.com>
  References: <393E6940.E3CDD1BD@ebeon.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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