ORACLE CONSTRAINT HELP

I have two tables table A AND Table B.
Table is a fact table and can be modified by business only.
Nothing can be done to table A.

I need to insert data in table B but have to check with data in Table A.
I have to make sure that data being inserted to table B must exist in table A.OR else it should throw error.

table A

id name loc
1  DELL  Austin
2  HP    houston
3  Sony  Japan
 
Table B
id name  ship
1  dell  comp
1  dell  server
2  XXX   XXX
3  YYY   YYY

So if i try to insert in id column of table B which is not in id column of table A,then  it should throw error.
like if i try
insert into B values (5,ZZZ,ZZZ);
then i should get error saying cannot insert 5 in TABLE B column ID.

PS: I cannot use foreign key relation. The constraint is pretty much foreign key ..But cannot use forgeing key as we dont want hassle while deleting from TABLE A. or B.

I suggest you take a look over here. It's a great resource for this .

HELLO arnzombie Jee,

I dont have much idea about Oracle but i will give you idea in  SQL.

- first create temp table and insert value in temp table first.
- Insert into B if only exists in A from temp table
-Finnaly drop temp table

Here is in sql

DECLARE @TEMP TABLE
(ID INT,
NAME VARCHAR(50),
SHIP VARCHAR(50)
)

INSERT INTO @TEMP VALUES(5,'ZZZ','ZZZ')

INSERT B

SELECT * FROM @TEMP T WHERE EXISTS(SELECT A.ID FROM A WHERE A.ID = T.ID)

It wont do any thing if Id is not exists in Table A. if You need error message you can use raseerr functuon

All the best.

Thanks for the help RAWBEE

your concept is great. I really like it.  Unfortuanately, i cannot imply that as i cant create temp table. Table B is being filled by the GUI. (frontend) . And they would need the same table to accss information. hence no posibility of creating temp table before inserting into B. I thought the other way around creating temp table for table A and apply foreign key logic.but it would again take a lotta time as the data in TAble A is enormous. Not a good idea.

create table B

 {

  id number,

name varchar2(20 Byte),

ship varchar2(20byte),

CONSTRAINT  "CK_ID" CHECK(id in(select id from A)) enable

}

I'm not sure if that works, but u can try that.

Thanks Techguy.. that was the first thing i tried but unfortunately you cannot have subquery in check constraint. So it dint work either.(NOT ALLOWED:CHECK(id in(select id from A))

It would be better to use a before insert trigger in your case. I think that would do the trick.

CREATE TRIGGER tr_tableB
BEFORE INSERT
ON TABLE B
AS
   v_name tableA.name%TYPE;
BEGIN
   SELECT name INTO v_name
   FROM tableA
   WHERE name = :new.name;
EXCEPTION WHEN no_data_found THEN
   -- you may want to insert it into audit table, log etc...
   RAISE;
END;

Not sure if this would compile. It might need lil modifications

 

Simple life , i really appreciate your help. Thats exactly wat i have been looking for.ANd wat i did.you are a genius....

YOU ROCK!!!!