mike wrote:
> 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?
What you describe is precisely what a Foreign Key (referential)
Constraint is used for. I don't understand why you think the NULL
would matter.
--
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)
Received on Mon Aug 29 2005 - 17:24:57 CDT
