Creating unique UNIQUE constraints in the database

The requirements of the recent project, to determine the only one piece of data, the original seems to have met, forget, and now record. Implementing a UNIQUE constraint may also not be unique to the primary key.

A UNIQUE constraint in Oracle is a means of ensuring that a class in a table, or a class in a table, is combined to be non-repetitive. We can create a unique constraint in Oracle by modifying the table when the table is created or created.

Here are some examples of creating unique constraints:

CREATE TABLE Unique_test

(ID number,

fname VARCHAR2 (20),

LName VARCHAR2 (20),

Address VARCHAR2 (100),

Email VARCHAR2 (40),

Constraint Name_unique unique (fname,lname))

Here we set up a table unique_test and combine the fname and lname together to create a unique constraint.

We can also add constraints manually by modifying the table after the table is created, for example:

ALTER TABLE Unique_test

Add constraint email_unique unique (email);

Below we insert data into the table,

Insert into Unique_test (id,fname,lname) VALUES (1, ' Tak Wah ', ' Liu ')

This line can be inserted as normal

Because we used to combine fname and lname as a constraint when we set up the table, because if we want to insert Andy Lau again,

Insert into Unique_test (Id,fname,lname) VALUES (2, ' Tak Wah ', ' Liu ')

The following error will occur:

ORA-00001: Violation of the unique constraint (sys.name_unique)

But if we change to the following values:

Insert into Unique_test (Id,fname,lname) VALUES (2, ' xueyou ', ' Zhang ');

It can also be inserted normally.

Some friends may have questions, we are not a unique constraint for email also established? Why aren't the two rows of data assigned to an email column, or two rows of e-mail columns that are empty values, and that the insert is successful?

This is because a null value (NULL) means that the current state of the column does not exist and that he will never be equal to another null value. So there's no violation of the sole constraint.

This article is from the "Java Program Ape Blog" blog, be sure to keep this source http://chengxuyuan.blog.51cto.com/5789198/1768512

Creating unique UNIQUE constraints in the database