Prevent duplicate data 3

[chicago1985]

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.

Please advise if this is possible?

 

[SantaMufasa]

Chicago,

Here is your existing table:

Enter table name: maintable

Col Column             Data Type     [Constraint Type: Name: En-/Dis-abled]
 #  Name               and Length    and Enforcement
--- ------------------ ------------- -------------------------------------------
  1 ID                 NUMBER        [PK:SYS_C0013635:ENABLED] NOT NULL/UNIQUE
  2 VALDATA            VARCHAR2(3)
  3 FID                NUMBER
  4 FID2               NUMBER

Here is code that ensures that the columns besides ID, in combination, will always be unique:

alter table maintable
            add constraint maintable_val_fid_fid2_uk unique (valdata, fid, fid2);

Table altered.

Col Column             Data Type     [Constraint Type: Name: En-/Dis-abled]
 #  Name               and Length    and Enforcement
--- ------------------ ------------- -------------------------------------------
  1 ID                 NUMBER        [PK:SYS_C0013635:ENABLED] NOT NULL/UNIQUE
  2 VALDATA            VARCHAR2(3)   [UK:MAINTABLE_VAL_FID_FID2_UK:ENABLED]
                                     UNIQUE

  3 FID                NUMBER        [UK:MAINTABLE_VAL_FID_FID2_UK:ENABLED]
                                     UNIQUE

  4 FID2               NUMBER        [UK:MAINTABLE_VAL_FID_FID2_UK:ENABLED]
                                     UNIQUE

SQL> insert into MainTable values (4,'zeo',25,43);
insert into MainTable values (4,'zeo',25,43)
*
ERROR at line 1:
ORA-00001: unique constraint (TEST.MAINTABLE_VAL_FID_FID2_UK) violated
*********************************************************************************

So the above illustrates proof of concept for rejecting a duplicate set of values on the three columns in combination.

Let us know if you have additional questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[chicago1985]

Thanks it worked great.

I assume in the future if I have 14 fields in one table I will have to manually input each one:

alter table maintable
            add constraint maintable_myNamehere unique (valdata, fid, fid2, fid3, anotherfld, another2, another3, another4, another5, another6, another7, another8, another9, another10);

And it would be easier in the future to create my constraints when I create my fields in the OEM client tool?

 

[SantaMufasa]

...I will have to manually input each one.

Correct.

...it would be easier in the future to create my constraints when I create my fields in the OEM client tool?

Perhaps, but not easier by much.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[chicago1985]

Thanks!