Home » SQL & PL/SQL » SQL & PL/SQL » how to check if primary key exists
how to check if primary key exists [message #439710] Tue, 19 January 2010 03:35 Go to next message
pyscho
Messages: 134
Registered: December 2009
Senior Member
I have a table called TRANS, and a primary key field tran_id. How would i check if there is a record matching tran_id 'DUP7927' ?
Re: how to check if primary key exists [message #439713 is a reply to message #439710] Tue, 19 January 2010 03:48 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Euhr... with a select...from...where constuction a.k.a. SQL query?

MHE
Re: how to check if primary key exists [message #439714 is a reply to message #439710] Tue, 19 January 2010 03:49 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
pyscho wrote on Tue, 19 January 2010 15:05
I have a table called TRANS, and a primary key field tran_id. How would i check if there is a record matching tran_id 'DUP7927' ?


IF you try to insert any duplicate value(if it has 'DUP7927' already exist)
oracle gives you
ERROR at line 1:
ORA-00001: unique constraint (user.constraint_name) violated

sriram Smile

Re: how to check if primary key exists [message #439716 is a reply to message #439714] Tue, 19 January 2010 03:55 Go to previous messageGo to next message
pyscho
Messages: 134
Registered: December 2009
Senior Member
how would i check a boolean var to check if the id existed because i want to do something like this

    SELECT tran_id
    FROM TRANS
    WHERE tran_id='DUP7927';
    
    IF (ID_EXISTS) THEN do something
    ELSE do something else
    END IF
Re: how to check if primary key exists [message #439719 is a reply to message #439716] Tue, 19 January 2010 03:58 Go to previous messageGo to next message
Michel Cadot
Messages: 63808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
DECLARE
  id integer;
  ...
BEGIN
  SELECT tran_id into id FROM TRANS WHERE tran_id='DUP7927';
  <do something>
EXCEPTION
  WHEN NO_DATA_FOUND THEN 
   <do other thing>
END;

Regards
Michel


Re: how to check if primary key exists [message #439727 is a reply to message #439719] Tue, 19 January 2010 04:50 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I'd opt for a cursor:
OPEN yourcursor;
FETCH yourcursor INTO local_variable;

IF yourcursor%FOUND Then
   <do_someting>
ELSE
   <do_other_thing>
END IF;

CLOSE yourcursor;
That way, you avoid that an exception is part of your regular logic (which is not the brightest of ideas IMHO).

MHE
Re: how to check if primary key exists [message #439743 is a reply to message #439727] Tue, 19 January 2010 05:24 Go to previous messageGo to next message
Michel Cadot
Messages: 63808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
That way, you avoid that an exception is part of your regular logic (which is not the brightest of ideas IMHO).

Well, according to Llewellyn there are 3 groups of exception: deliberate, unfortunate, and unexpected.

Widely quoting something from someone from somewhere I didn't remember but keep in a file for memory:

In deliberate exceptions, the code deliberately raises an exception as part of its normal behavior. This is the case of NO_DATA_FOUND if you want an IF THEN ELSE behaviour or when you read a file as it marks the end of the file.

Unfortunate exceptions are those where an exception has been raised that may not constitute an error in your application logic. This is a different case of NO_DATA_FOUND, for instance, when a query returns no row when you expect one but you know how to handle this case.

Unexpected exceptions are the other ones, you do not expect they come and even don't think how to handle them. This is the case of TOO_MANY_ROWS exception in a query for instance.

Regards
Michel


Re: how to check if primary key exists [message #439747 is a reply to message #439743] Tue, 19 January 2010 05:31 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Laughing I should have know you'd come up with something like that. I still tend to avoid using no_data_found etc. as part of my logic. If I can avoid it, I will. But let's not digress too far.

Thanks for the pointer though!

MHE
Re: how to check if primary key exists [message #440439 is a reply to message #439716] Sun, 24 January 2010 17:25 Go to previous messageGo to next message
shahidmughal
Messages: 91
Registered: January 2006
Location: Faisalabad Pakistan
Member

hi,
i hope you are fine.

try this code also.

there will be no need to use the exception.

declare
a number;

begin
select count(tran_id)
into a
from trans
where tran_id='DUP7927';

IF A = 0 THEN
<DO SOME THING >
ELSE
<DO OTHER THING>
END IF;

END;

i hope this one is also a good choice.

Regards

Muhammad Shahid Mughal
Oracle Developer
Faisalabad Pakistan
Re: how to check if primary key exists [message #440447 is a reply to message #440439] Sun, 24 January 2010 23:40 Go to previous messageGo to next message
Michel Cadot
Messages: 63808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags.
Use the "Preview Message" button to verify.

2/ Counting ALL rows to know if there is ONE it is a bad idea. The best ways have been done.

Regards
Michel

Re: how to check if primary key exists [message #440455 is a reply to message #439710] Mon, 25 January 2010 00:40 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
I think Using Exists will be more faster for Pk check rather then selecting the Table Record in the variable.


create table Pk_test as select rownum as PK from dual connect by level < 100000;

alter table pk_test add primary key(PK);

DECLARE
  id NUMBER ;
BEGIN
select 1 into id from dual where exists ( select 1 from pk_test where PK = 55555);
DBMS_OUTPUT.PUT_LINE('EXISTS');
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('NOT EXISTS');
END;
/


above exists query execution plan.

Execution Plan
----------------------------------------------------------
Plan hash value: 1906259202

-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |     1 |       |     3   (0)| 00:00:01 |
|*  1 |  FILTER            |              |       |       |            |          |
|   2 |   FAST DUAL        |              |     1 |       |     2   (0)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| SYS_C0039342 |     1 |    13 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter( EXISTS (SELECT /*+ */ 0 FROM "PK_TEST" "PK_TEST" WHERE
              "PK"=55555))
   3 - access("PK"=55555)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        216  bytes sent via SQL*Net to client
        247  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed



Hope this Helps. Cool

Re: how to check if primary key exists [message #440627 is a reply to message #439710] Tue, 26 January 2010 05:40 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
Given the choice of using a predefined CURSOR and IF/THEN/ELSE or a singleton select w/NO_DATA_FOUND exception handler, I'd go with the NO_DATA_FOUND every time.

Kevin
Re: how to check if primary key exists [message #440673 is a reply to message #439710] Tue, 26 January 2010 10:18 Go to previous messageGo to next message
daverich
Messages: 23
Registered: January 2010
Location: UK
Junior Member
Just use this code. you get your boolean variable for your manipulation. Whenever you have got a record it will be true otherwise it will be false.



DECLARE
id_exists BOOLEAN := FALSE;
BEGIN

FOR l_rec IN (SELECT tran_id FROM TRANS WHERE tran_id='DUP7927') LOOP
id_exists := TRUE;
END LOOP;

IF (id_exists) THEN do something
ELSE do something else
END IF
END;



Cheers
Dave

[Updated on: Tue, 26 January 2010 10:23] by Moderator

Report message to a moderator

Re: how to check if primary key exists [message #440676 is a reply to message #440673] Tue, 26 January 2010 10:23 Go to previous messageGo to next message
Michel Cadot
Messages: 63808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: how to check if primary key exists [message #440679 is a reply to message #440673] Tue, 26 January 2010 10:31 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
daverich wrote on Tue, 26 January 2010 16:18
Just use this code. you get your boolean variable for your manipulation. Whenever you have got a record it will be true otherwise it will be false.



DECLARE
id_exists BOOLEAN := FALSE;
BEGIN

FOR l_rec IN (SELECT tran_id FROM TRANS WHERE tran_id='DUP7927') LOOP
id_exists := TRUE;
END LOOP;

IF (id_exists) THEN do something
ELSE do something else
END IF
END;



Cheers
Dave


You'd never get that past a code review at my place. Loops should be used for processing mulitiple records not one.
Re: how to check if primary key exists [message #440686 is a reply to message #440679] Tue, 26 January 2010 10:57 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I've run into code that does that sort of loop processing - it drives me up the wall - you have to keep checking table definitions when you're debugging to see if this query should return one or many rows.

Re: how to check if primary key exists [message #440687 is a reply to message #440686] Tue, 26 January 2010 11:00 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Yep, that's why I rewrite anything like that whenever I see it.
Re: how to check if primary key exists [message #440690 is a reply to message #440679] Tue, 26 January 2010 11:02 Go to previous messageGo to next message
daverich
Messages: 23
Registered: January 2010
Location: UK
Junior Member
Quote:
You'd never get that past a code review at my place. Loops should be used for processing mulitiple records not one.


There is no such rule that I can remember in Oracle books. Different companies uses different coding standards - that does not signify someones's code is bad just because it does not conform to your company standard practice.

Whatever path you chose you must execute a select statement - which in turn will open a implicit or explicit cursor. That is exactly what my code is doing. Either way you have to use a IF...THEN...ELSE or OPEN cursor or FOR ...LOOP.... the aim is same and it just need one cursor.

Why do you think FOR..LOOP is not suited - what's wrong with that? Everybody knows what is a loop - people just need to know how to use them .

Cheers
Dave





Re: how to check if primary key exists [message #440694 is a reply to message #440686] Tue, 26 January 2010 11:11 Go to previous messageGo to next message
daverich
Messages: 23
Registered: January 2010
Location: UK
Junior Member
Quote:
I've run into code that does that sort of loop processing - it drives me up the wall - you have to keep checking table definitions when you're debugging to see if this query should return one or many rows.


We are talking about a primary key value - so you will never ever get more than one record from that table for that vaule in Oracle (as long as the primary key is there). I agree if this is not a primary key select then the code can fail. But my answer and code is for this particular situation.

Cheers
Dave

Re: how to check if primary key exists [message #440698 is a reply to message #440690] Tue, 26 January 2010 11:28 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
daverich wrote on Tue, 26 January 2010 17:02
Quote:
You'd never get that past a code review at my place. Loops should be used for processing mulitiple records not one.


There is no such rule that I can remember in Oracle books. Different companies uses different coding standards - that does not signify someones's code is bad just because it does not conform to your company standard practice.

Whatever path you chose you must execute a select statement - which in turn will open a implicit or explicit cursor. That is exactly what my code is doing. Either way you have to use a IF...THEN...ELSE or OPEN cursor or FOR ...LOOP.... the aim is same and it just need one cursor.

Why do you think FOR..LOOP is not suited - what's wrong with that? Everybody knows what is a loop - people just need to know how to use them .

Cheers
Dave



Loops loop. You've written one that can't by design.
Yes it is a matter of taste to a degree, which is why we've already got several answers on here.
Me and JRowbottom don't like your approach because it's not as explicit as the others as to what it's doing, at least to our minds.
To each their own I guess.
Re: how to check if primary key exists [message #440699 is a reply to message #440694] Tue, 26 January 2010 11:33 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
But the code is inherrently unclear unless you know the application.

Without knowing in advance what the primary and unique keys are for the table, you can't know whether the query should only return a single row.

If you code it like this:
DECLARE
  v_id_exists BOOLEAN := TRUE;
  v_tran_id   trans.tran_id%type;
BEGIN

  BEGIN
    SELECT tran_id
    INTO   v_tran_id 
    FROM   TRANS 
    WHERE   tran_id= 'DUP7927';

  EXCEPTION
    WHEN no_data_found then
      v_id_exists := false;
  END;

  IF (id_exists) THEN do something
  ELSE do something else
  END IF
END;

Then it's clear that the query is intended to return a single row.

It just makes it easier for the developer after you to pick the code up.

Personally, I'd rather see the whole block as a function called TRAN_ID_EXISTS, with a set of comments that made it's function unequivocally clear.
Re: how to check if primary key exists [message #440824 is a reply to message #440699] Wed, 27 January 2010 03:49 Go to previous messageGo to next message
daverich
Messages: 23
Registered: January 2010
Location: UK
Junior Member
Quote:
DECLARE
v_id_exists BOOLEAN := TRUE;
v_tran_id trans.tran_id%type;
BEGIN

BEGIN
SELECT tran_id
INTO v_tran_id
FROM TRANS
WHERE tran_id= 'DUP7927';

EXCEPTION
WHEN no_data_found then
v_id_exists := false;
END;

IF (id_exists) THEN do something
ELSE do something else
END IF
END;


Your code is fine but there is still scope to improve.

You said
Quote:
Without knowing in advance what the primary and unique keys are for the table, you can't know whether the query should only return a single row.


The code you wrote also requires the developer to know in advance "the primary and unique keys are for the table". Otherwise how will he know the SELECT ... INTO will return just one row? What about if it returns more than 1 row (i.e 2 or 3 rows). Then the code will fail. Without handling that exception the code will just crash, right?

You can change that to

  BEGIN
    SELECT tran_id
    INTO   v_tran_id 
    FROM   TRANS 
    WHERE   tran_id= 'DUP7927';

  EXCEPTION
    WHEN no_data_found THEN
      v_id_exists := false;
    WHEN too_many_rows THEN
      null;
  END;


As anybody here will know, coding standard is used just for guidance. There are always multiple techniques to implement one business requirement.


[Updated on: Wed, 27 January 2010 03:51]

Report message to a moderator

Re: how to check if primary key exists [message #440826 is a reply to message #440824] Wed, 27 January 2010 04:04 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can tell that my query is intended to return a single row because it uses SELECT...INTO.
NO_DATA_FOUND is handled and not re-raised, so it is obviously an exception that is expected to occur as part of the normal operation.
TOO_MANY_ROWS is not handled, and is therefore not an exception that should happen as part of normal operations.

By adding a TOO_MANY_ROWS exception handler you confuse the situation, and make it look as though this query can be expected to return multiple rows, and that we don't care about this - exactly the opposite effect to what we want, and exactly the same problem that your original code had.

Rather than add an exception handler for too many rows, I'd say you should perhaps add a comment in the Exception block pointing out that TOO_MANY_ROWS should not occurr.
Re: how to check if primary key exists [message #440904 is a reply to message #440694] Wed, 27 January 2010 21:17 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
daverich wrote on Tue, 26 January 2010 11:11

We are talking about a primary key value - so you will never ever get more than one record from that table for that vaule in Oracle (as long as the primary key is there). I agree if this is not a primary key select then the code can fail. But my answer and code is for this particular situation.

Cheers
Dave




Dave :- why to keep room to fail the code ? , Your approach is not wrong but as JRowbottom explain I think it would not be advisable to write.
Re: how to check if primary key exists [message #440905 is a reply to message #439710] Wed, 27 January 2010 21:33 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
The only thing I would say is that as a general rule, do not trap an error with an exception handler unless you actually plan on handling the error. To that end, WHEN TOO_MANY_ROWS THEN NULL; is a mistake. Other than that, the Dave code is what I would use.

I guess that makes it the same code as the JROWBOTTOM code.

Kevin

[Updated on: Wed, 27 January 2010 21:35]

Report message to a moderator

Re: how to check if primary key exists [message #444102 is a reply to message #440905] Thu, 18 February 2010 12:52 Go to previous message
cristi_Buc
Messages: 12
Registered: February 2010
Location: Bucharest
Junior Member

If you want to insert/ update in function of the existence of the PK, you can use MERGE statement. This command is useful in some cases.
Previous Topic: Working days
Next Topic: Trying to create a Counter for the number of inserts run in a block
Goto Forum:
  


Current Time: Wed Sep 28 17:44:05 CDT 2016

Total time taken to generate the page: 0.18975 seconds