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