Create a rule in ORACLE SQL to prevent insert of duplicate data-CodePudding

I need to create a constraint for the table below, following the rule that the joining of CODE and CAR columns must be unique, in others words, if someone makes an insert in the table below with CODE = 7 and CAR = 'AUTO' the rule should prevent this record from being inserted and return an error that I can customize with the message "CODE & CAR values must be unique".

I'm doing it in ORACLE SQL and I believe that constraint is not the better way for this task.

How can I do it?

Thanks!

CODE CAR
------------
1    AUTO
1    MANUAL
3    AUTO
3    MANUAL
5    AUTO
5    MANUAL
7    AUTO
7    MANUAL

CodePudding user response：

To me, the simplest way is to create unique index.

SQL> create table car (code number, car varchar2(20));

Table created.

SQL> create unique index code_and_car_must_be_unique on car (code, car);

Index created.

SQL> insert into car (code, car) values (1, 'Auto');

1 row created.

SQL> insert into car (code, car) values (1, 'Auto');
insert into car (code, car) values (1, 'Auto')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.CODE_AND_CAR_MUST_BE_UNIQUE) violated


SQL>

Or, as @astentx commented, using a constraint (result is the same, though):

SQL> drop index code_and_car_must_be_unique;

Index dropped.

SQL> alter table car add constraint code_and_car_must_be_unique
  2    unique (code, car);

Table altered.

SQL> insert into car (code, car) values (1, 'Auto');
insert into car (code, car) values (1, 'Auto')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.CODE_AND_CAR_MUST_BE_UNIQUE) violated


SQL>