SV: Uniqueness

From: Jesper Haure Norrevang <jhn.aida_at_cbs.dk>
Date: Wed, 18 May 2005 14:15:59 +0200
Message-id: <000001c55ba3$59ef0280$4a28e282@AIDA.local>

Kean,

You can do something like:

select decode(count(*),

              0, 'Unique',
              'NON-Unique')

from mytbl
where id =3D 2;

But please don't try to write your own code for simple Primary Key and/or Foreign Key purposes. If your data model says, that a column needs to be unique, then declare an unique constraint and let Oracle manage the integrity.

Your own code might fail, e.g. if two concurrent transactions are running.

SESSION 1.   Check if ID=3D2 exists.
  OK it did not exist, insert row with ID =3D 2   (and don't commit).

SESSION 2   Check if ID=3D2 exists.=20
  (Session 2 cannot see the row, because session 1    has not yet committed).
  OK it did not exist, insert row with ID =3D 2   Commit;

SESSION 1.   Commit;

select *
from mytbl
where ID =3D 2;

Now we have duplicate IDs committed into the database, even though we thought, that we tested before inserting them each of them!

Regards
Jesper Haure Norrevang

-----Oprindelig meddelelse-----

Fra: oracle-l-bounce_at_freelists.org =
[mailto:oracle-l-bounce_at_freelists.org] P=E5 vegne af Kean Jacinta
Sendt: 18. maj 2005 08:12
Til: oracle-l_at_freelists.org
Emne: Uniqueness

Hi again,

Is there a way to check for uniqueness of a value ? Apart from set the column to be unique ?

For example :

Table : mytbl
PK ID

---

1
2
3

Insert into mytbl (id) values (2);
=20
Oracle will give error.=20

---

Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around=20 http://mail.yahoo.com=20
--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Wed May 18 2005 - 08:20:46 CDT