[Show all top banners]

arnzombie
Replies to this thread:

More by arnzombie
What people are reading
Subscribers
:: Subscribe
Back to: Computer/IT Refresh page to view new replies
 ORACLE Constraints help
[VIEWED 3510 TIMES]
SAVE! for ease of future access.
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.


 
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 .
 
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.


 
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.


 
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.


 
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))
 
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

 
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!!!!


 


Please Log in! to be able to reply! If you don't have a login, please register here.

YOU CAN ALSO



IN ORDER TO POST!




Within last 365 days
Recommended Popular Threads Controvertial Threads
Lets play Antakshari...........
शीर्षक जे पनि हुन सक्छ।
NRN card pros and cons?
What are your first memories of when Nepal Television Began?
TPS Re-registration
Democrats are so sure Trump will win
is Rato Bangala school cheating?
What Happened to Dual Citizenship Bill
Basnet or Basnyat ??
We live in precarious times
H1B fraud
nrn citizenship
इन्दिरा जोशीको चिन्ता लौच
Sajha has turned into MAGATs nest
Nas and The Bokas: Coming to a Night Club near you
श्राद्द
सेक्सी कविता - पार्ट २
डलराँ कमाएर ने .रु मा उडांउदा !@#
ChatSansar.com Naya Nepal Chat
Why always Miss Nepal winner is Newari??
Nas and The Bokas: Coming to a Night Club near you
राजदरबार हत्या काण्ड बारे....
Mr. Dipak Gyawali-ji Talk is Cheap. US sends $ 200 million to Nepal every year.
Harvard Nepali Students Association Blame Israel for hamas terrorist attacks
TPS Update : Jajarkot earthquake
is Rato Bangala school cheating?
NOTE: The opinions here represent the opinions of the individual posters, and not of Sajha.com. It is not possible for sajha.com to monitor all the postings, since sajha.com merely seeks to provide a cyber location for discussing ideas and concerns related to Nepal and the Nepalis. Please send an email to admin@sajha.com using a valid email address if you want any posting to be considered for deletion. Your request will be handled on a one to one basis. Sajha.com is a service please don't abuse it. - Thanks.

Sajha.com Privacy Policy

Like us in Facebook!

↑ Back to Top
free counters