Subquery not allowed here

From: mike <hillmw_at_charter.net>
Date: 29 Aug 2005 15:15:34 -0700
Message-ID: <1125353734.264365.292620@g44g2000cwa.googlegroups.com>

I have 2 tables and I want to make sure that a value from tableb is included in tablea before it is entered.

tables defined as:

create tablea as
(

SUPPLIER_ID		INTEGER	NOT NULL,
SUPPLIER_NAME		VARCHAR(50) NOT NULL,
SUPPLIER_LOCATION	VARCHAR(50) NOT NULL

)

create tableb
(

LDAP_UID	VARCHAR(40) NOT NULL,
ACCESS_ID	INTEGER	NOT NULL,
LM_FLAG		CHAR(1) NOT NULL,
SUPPLIER_ID	INTEGER

)

I can't use a reference from tableb to tablea because the supplier_id could be null in tableb, but nulls can not exist in tablea

I tried to use a check constraint like the below but got the error message "Subquery not allowed here".

ADD CONSTRAINT CK_SUPPLIER_ID check (SUPPLIER_ID in (select supplier_id from tablea))

Any other ideas? Received on Mon Aug 29 2005 - 17:15:34 CDT