I would like to make sure there are no duplicate data entries in my
Oracle 9i table (called MainTable) which has an Id field that is the
primary key, ValData with a varchar data type, Fid and Fid2 are number
data types.


Id ValData Fid Fid2
1 abc 34 2
2 efg 23 34
3 zeo 25 43

Sometimes someone can enter a duplicate ValData, Fid and Fid2 and it
will end up like this:


Id ValData Fid Fid2
1 abc 34 2
2 efg 23 34
3 zeo 25 43
4 zeo 25 43


What constraints or restrictions can I place on the MainTable where it
will never allow a duplicate entry into the table?
I would like to do this somehow in the database. If someone tries to
enter a duplicate I should get a error message or something to
indicate an attempt to enter duplicate data.

"teser3 (AT) hotmail (DOT) com" <teser3 (AT) hotmail (DOT) com> wrote in
news:1193009869.171884.198420 (AT) e9g2000prf (DOT) googlegroups.com:

http://www.orafaq.com/forum/t/91265/74940/

Comments embedded.
On Oct 21, 6:37 pm, "tes... (AT) hotmail (DOT) com" <tes... (AT) hotmail (DOT) com> wrote:
Yes, because the only unique value for this data is the Id.

You'll need a unique constraint on the remaining three columns:

alter table MainTable add constraint MainTable_uq unique(ValData, Fid,
Fid2);

which would prevent any existing combination of ValData, Fid and Fid2
values to exist more than once in the table. Of course this doesn't
make each individual column unique, as the combination of zeo, 25, 44
would be allowed, as would zeo, 26, 43 and zoe, 25, 43. If you truly
want unique values across all columns and records then you'd need
unique constraints on EACH column:

alter table MainTable add constraint MainTable_Val_Uq unique(ValData);
alter table MainTable add constraint MainTable_Fid_Uq unique(Fid);
alter table MainTable add constraint MainTable_Fid2_Uq unique(Fid2);

This would prevent ValData from duplicating the 'zeo' value in the
table, as well as not allowing another Fid value of 25 or another Fid2
value of 43 (which, I think, you do NOT want). My recommendation is
to add the unique constraint on the combination of remaining columns
(the first example I provided).

And having that combined unique constraint will do just that.


David Fitzjarrell