Unique Records on a Column

Introduction

One of the primary concerns of records is their uniqueness. In a professional database, you usually want to make sure that each record on a table is unique. There are various ways you can solve this problem.

   

Creating a Uniqueness Rule

To assist you with creating a columns whose values will be distinguishable, you can use the UNIQUE operator. To apply it on a column, after the data type, type UNIQUE. Here is an example:

SQL> CREATE TABLE Employees
  2  (
  3     EmployeeNumber nchar(6) UNIQUE,
  4     FirstName nvarchar2(20),
  5     LastName nvarchar2(20),
  6     HourlySalary number(6, 2)
  7  );

Table created.

SQL>

When a column has been marked as unique, during data entry, the user must provide a unique value for each new record. If an existing value is assigned to the column, this would produce an error. Here is an example:

SQL> INSERT INTO Employees
  2  VALUES('24880', 'John', 'Nichols', 15.50);

1 row created.

SQL> INSERT INTO Employees
  2  VALUES('92846', 'Rénée', 'Almonds', 20.22);

1 row created.

SQL> INSERT INTO Employees
  2  VALUES('47196', 'Peter', 'Sansen', 28.04);

1 row created.

SQL> INSERT INTO Employees
  2  VALUES('92846', 'Mhadi', 'Camara', 12.96);
INSERT INTO Employees
*
ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.SYS_C009980) violated

SQL>