SV: Uniqueness
- From: Jesper Haure Norrevang <jhn.aida@xxxxxx>
- To: jacintakean@xxxxxxxxx
- Date: Wed, 18 May 2005 14:15:59 +0200
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@xxxxxxxxxxxxx =
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] P=E5
vegne af Kean Jacinta
Sendt: 18. maj 2005 08:12
Til: oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
