How to delete unique constraint in oracle

In Oracle, you can use the "ALTER TABLE" statement and the "DROP CONSTRAINT" keyword to delete unique constraints. The syntax is "ALTER TABLE data table name DROP CONSTRAINT unique constraint name;".

The operating environment of this tutorial: Windows 7 system, Oracle 11g version, Dell G3 computer.

Oracle unique constraint

Unique constraint means that the value of the field in all records cannot appear repeatedly.

A unique constraint is an integrity constraint that ensures that the data stored in a column or set of columns is unique among rows in the table.

Typically, when creating a table using inline constraint syntax, a unique constraint is applied to a column as follows:

CREATE TABLE table_name (
    ...
    column_name data_type UNIQUE
    ...
);

This unique constraint specifies the value in column_name Unique in the entire table.

You can specify a unique constraint name by using the CONSTRAINT clause and the constraint name:

CREATE TABLE table_name (
    ...
    column_name data_type CONSTRAINT unique_constraint_name UNIQUE
    ...
);

And if you want to delete a unique constraint:

ALTER TABLE 数据表名
DROP CONSTRAINT 唯一约束名;

Example:

Create a table named clients:

CREATE TABLE clients (
    client_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    first_name VARCHAR2(50) NOT NULL,
    last_name VARCHAR2(50) NOT NULL,
    company_name VARCHAR2(255) NOT NULL,
    email VARCHAR2(255) NOT NULL UNIQUE,
    phone VARCHAR(25)
);

The email column has a unique constraint to ensure there are no duplicate emails.

The following statement inserts a row in the clients table:

INSERT INTO clients(first_name,last_name, email, company_name, phone)
VALUES('Christene','Snider','we.chen@oraok.com', 'ABC Inc', '408-875-6075');

Now, we try to insert an email value in the email column that already exists New row:

INSERT INTO clients(first_name,last_name, email, company_name, phone)
VALUES('Sherly','Snider','we.chen@oraok.com', 'ABC Inc', '408-875-6076');

Oracle issues the following error message indicating a uniqueness constraint violation:

SQL Error: ORA-00001: unique constraint (OT.SYS_C0010726) violated

If you want two columns: company_name and phoneTo add unique constraints, you can use the following ALTER TABLE statement:

ALTER TABLE clients
ADD CONSTRAINT unique_company_phone UNIQUE(company_name, phone);

As a result, the combination of values ​​in the company_name and phone columns is clients Is unique among the rows in the table.

To delete the unique constraint UNIQUE_COMPANY_PHONE, please use the following statement:

ALTER TABLE clients
DROP CONSTRAINT unique_company_phone;

Recommended tutorial: "Oracle Tutorial"

The above is the detailed content of How to delete unique constraint in oracle. For more information, please follow other related articles on the PHP Chinese website!