[VIEWED 4031
TIMES]
|
SAVE! for ease of future access.
|
|
|
arnzombie
Please log in to subscribe to arnzombie's postings.
Posted on 06-12-08 10:46
AM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
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.
|
|
|
|
pyaradeshbasiharu
Please log in to subscribe to pyaradeshbasiharu's postings.
Posted on 06-12-08 11:04
AM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
I suggest you take a look over here. It's a great resource for this .
|
|
|
rawbee
Please log in to subscribe to rawbee's postings.
Posted on 06-12-08 11:47
AM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
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.
|
|
|
arnzombie
Please log in to subscribe to arnzombie's postings.
Posted on 06-12-08 12:35
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
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.
|
|
|
techGuy
Please log in to subscribe to techGuy's postings.
Posted on 06-12-08 12:42
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
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.
|
|
|
arnzombie
Please log in to subscribe to arnzombie's postings.
Posted on 06-12-08 2:59
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
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))
|
|
|
simple_life
Please log in to subscribe to simple_life's postings.
Posted on 06-13-08 9:16
AM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
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
Last edited: 13-Jun-08 09:23 AM
Last edited: 13-Jun-08 09:25 AM
|
|
|
arnzombie
Please log in to subscribe to arnzombie's postings.
Posted on 06-13-08 10:51
AM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
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!!!!
|
|