Oracle unique constraints and oracleunique Constraints
A unique constraint is a unique constraint that specifies that duplicate values are not allowed in column types. You can add a unique constraint for a single column or a unique constraint for multiple columns (Table-level constraints ). If you add a unique constraint to multiple columns, you only need to ensure that the values of these columns are not all the same.
When creating a table, add the unique constraint to the column in the following format:
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 raise the Naji unique constraint for the columns in the created table. In this case, you need to use the alter talbe .. add statement in the following format:
Alter table table_name add [constraint constraint_name] unique (column_name );
Delete a unique constraint
To delete the unique constraint on a column, you can use the alter table... drop statement in the following format:
Alter table table_name drop unique (column_name)
If the constraint has a name, you can delete the constraint by specifying a name. The statement format is as follows:
Alter table drop constraint constraint_name;
The specific operations are as follows:
SQL> create table person
2 (pid number (4) not null unique,
3 pname varchar2 (10) unique,
4 sex char (2)
5 );
The table has been created.
Create a person table above and specify the unique constraint for both pid and pname
SQL> insert into person values (1, 'aaa', 'femal ');
One row has been created.
SQL> insert into person values (2, 'aaa', 'femal ');
Insert into person values (2, 'aaa', 'femal ')
*
Row 3 has an error:
ORA-00001: violation of unique constraints (SYSTEM. SYS_C0010079)
SQL> insert into person values (2, 'abcd', 'female ');
One row has been created.
As shown above, an error is reported when the added name is the same.
SQL> insert into person values (2, 'abcd', 'female ');
1 row created
If you modify the pname, no error is reported.
Create another table.
SQL> create table p2 (
2 pid number (4 ),
3 pname varchar2 (10 ),
4 bytes X char (2 ),
5 constraint p2_unique unique (pid, pname)
6 );
The table has been created.
SQL> alter table p2 add constraint unique_p2sex unique (sort X); -- add unique for sort x
The table has been changed.
SQL> alter table p2 drop unique (Limit X); -- delete the unique constraint
The table has been changed.
SQL> alter table p2 drop constraint p2_unique;
The table has been changed.
SQL>