[SQL] Problem with subquery in CHECK constraint.
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,
NAMEVARCHAR(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,
NAMEVARCHAR(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,
NAMEVARCHAR(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: [EMAIL PROTECTED]
phone: (087) 8052390
Re: [SQL] Problem with subquery in CHECK constraint.
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
[SQL] ORDER BY in definition of views
Guys, Does anyone know if this is on the radar for 7.1? Niall -- Niall Smart email: [EMAIL PROTECTED] phone: (087) 8052390
Re: [SQL] ORDER BY in definition of views
Michael Ansley wrote: > > Would there be a particular reason to do this? > I like to define views for lookups (i.e. job titles, departments, accounts, whatever). It would be nice if I could just do a select * from whatever_vw instead of having to remember to put the order by whatever_name clause. Admittedly its no biggie, but it is nice-to-have. Niall
Re: [SQL] Problem with subquery in CHECK constraint.
Hiroshi Inoue wrote:
> > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]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
[SQL] Something I'd like to try...
Hi, I just noticed that postgres doesn't totally support column aliases on UPDATE statements, for example UPDATE EMPLOYEES SET OFFICE_PHONE = UU.OFFICE_PHONE, MOBILE_PHONE = UU.MOBILE_PHONE, OFFICE_CD = UU.OFFICE_CD, ABOUT_ME = UU.ABOUT_ME FROM UNCONFIRMED_UPDATES UU WHERE EMPLOYEES.EMPLOYEE_ID = UU.EMPLOYEE_ID AND UU.UPDATE_ID = 'HJhjaJ023J19KJAqp' It is not currently possible to alias EMPLOYEES so that the test can become E.EMPLOYEE_ID = UU.EMPLOYEE_ID Do the guru's think that this would be hard to add? Also, is it desirable? -- Niall Smart email: [EMAIL PROTECTED] phone: (087) 8052390
