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
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