Re: Prevent duplicate data entry

Comments embedded.
On Oct 21, 6:37 pm, "tes..._at_hotmail.com" <tes..._at_hotmail.com> wrote:
> 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
>

Yes, because the only unique value for this data is the Id.

> What constraints or restrictions can I place on the MainTable where it
> will never allow a duplicate entry into the table?

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

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

And having that combined unique constraint will do just that.

David Fitzjarrell Received on Mon Oct 22 2007 - 08:44:29 CDT