Create unique unique constraints in the database

Requirements in the recent project, confirm the only data, originally seems to have encountered, forgot, now record it. It is also possible to implement unique constraints that are not unique for primary keys.

A unique constraint in an oracle is a means of ensuring that a class in a table, or a combination of classes in a table, is not duplicated. We can create unique constraints in oracle by modifying the table when or after it is 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 create a table unique_test and combine fname and lname 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,'Dehu',' Liu')

This line can be inserted normally

Because we created the table by combining fname and lname as a constraint, because if we want to insert Andy Lau again

insert into unique_test(id,fname,lname) values(2,'Dehu',' Liu')

The following errors will occur:

ORA-00001: Unique constraint violation (SYS.NAME_UNIQUE)

But if we change it to the following value:

insert into unique_test(id,fname,lname) values(2,'Xueyou',' Zhang');

You can insert it normally again.

Some of you may wonder, aren't we creating a unique constraint for email too? Why is it that neither of these two rows of data assigns a value to the email column, that is, the email column of either or both rows is null, and the insertion is successful?

This is because a null value means that the current state of the column does not exist, and it can never be equal to another null value. So there is no violation of the unique constraint.