Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Subquery not allowed here

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US