Re: Subquery not allowed here

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: Mon, 29 Aug 2005 23:04:20 +0000 (UTC)
Message-ID: <df049j$d97$1@klatschtante.init7.net>

On 2005-08-29, mike <hillmw_at_charter.net> wrote:
> Sometime there is not a supplier_id!!
>
> If I default it to 0, then I'll have to put a dummy 0 in tablea.
>
> Mike

No need to put a dummy 0 in tablea:

create table tablea (

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

);

create table tableb (

  LM_FLAG         CHAR(1) NOT NULL,
  SUPPLIER_ID     references tablea

);

insert into tablea values (1,'New York Tomatos LTD', 'New York City'); insert into tablea values (2,'West Coast Shirts' , 'Los Angeles' );

insert into tableb values ('c', 1   );
insert into tableb values ('d', 2   );
insert into tableb values ('e', 2   );

-- The following statement works perfectly 

-- tableb.SUPPLIER_ID is not 'NOT NULL':

--
insert into tableb values ('f', null);

-- This statment gives an...
insert into tableb values ('g', 3   );
-- ORA-02291: integrity constraint violated - parent key not
-- found

hth
Rene


-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch/

Received on Mon Aug 29 2005 - 18:04:20 CDT