A unique constraint is a unique constraint that requires a column type to not allow duplicate values. You can add a unique constraint for a separate column, or you can add a unique constraint (which is a table-level constraint) for multiple columns. If you add a unique constraint for more than one column, you only need to ensure that the values of the columns are not all the same.
When creating a table, add a unique constraint to the column in the following form:
column_name data_type [constraint constraint_name] unique or
CREATE TABLE table_name (
column_name data_type,[,...]
[Constraint constraint_name] Unique (column_name)
[,...]
)
You can also constrain Lietina in a table you've created, and you'll need to use alter TALBE. The Add statement is in the following form:
ALTER TABLE table_name ADD [constraint constraint_name] Unique (column_name);
Delete a unique constraint
To delete a unique constraint on a column, you can use the ALTER TABLE...DROP statement in the following form:
ALTER TABLE table_name drop unique (column_name)
If the constraint has a name, you can also delete the constraint by using the specified name, as follows:
ALTER TABLE table drop constraint constraint_name;
The following are the specific actions:
sql> CREATE TABLE person
2 (PID number (4) is not null unique,
3 pname varchar2 (Ten) Unique,
4 Sex char (2)
5);
The table is created.
Create a person table above and specify a unique constraint for both PID and PName
Sql> INSERT into the person values (1, ' aaa ', ' female ');
1 rows have been created.
Sql> INSERT into the person values (2, ' aaa ', ' female ');
INSERT into person values (2, ' aaa ', ' female ')
*
An error occurred on line 1th:
ORA-00001: Violates the unique constraint (SYSTEM. sys_c0010079)
Sql> INSERT into the person values (2, ' abcd ', ' female ');
1 rows have been created.
As shown above, when adding the same name as the Times wrong.
Sql> INSERT into the person values (2, ' abcd ', ' female ');
1 rows have been created
Modify the next pname, this time there is no error.
Then create another table.
Sql> CREATE TABLE P2 (
2 PID Number (4),
3 pname varchar2 (10),
4 Psex char (2),
5 constraint P2_unique unique (pid,pname)
6);
The table is created.
Sql> ALTER TABLE p2 add constraint unique_p2sex unique (psex); --Add unique to Psex
The table has changed.
sql> ALTER TABLE P2 drop unique (psex); --delete unique UNIQUE constraint
The table has changed.
sql> ALTER TABLE P2 drop constraint p2_unique;
The table has changed.
Sql>
Unique constraints for Oracle
