How to delete constraints in oracle

Delete method: 1. Use the "ALTER TABLE table name DROP UNIQUE (field name);" statement to delete a single field constraint of the table; 2. Use the "ALTER TABLE table name DROP CONSTRAINT constraint name;" statement to delete the table Multiple field constraints.

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

How to delete constraints in oracle

Delete a single field constraint of the table

SQL> ALTER TABLE 表名 DROP UNIQUE(字段名);

Delete multiple field constraints of the table

SQL> ALTER TABLE 表名 DROP CONSTRAINT 约束名;

Extended knowledge:

Operations (setting, deletion, query) on table field constraints in Oracle database

For the table Add constraints to a single field

SQL> ALTER TABLE 表名 ADD UNIQUE(字段名);

Add constraints to multiple fields in the table

SQL> ALTER TABLE 表名 ADD CONSTRAINTS 约束名 UNIQUE(字段名, 字段名 ...);

Query what constraints a certain table has

SQL> select CONSTRAINT_NAME from USER_CONSTRAINTS WHERE TABLE_NAME='表名' AND CONSTRAINT_TYPE='U';

Query which fields are constrained by a certain constraint

SQL> select COLUMN_NAME from USER_CONS_COLUMNS WHERE CONSTRAINT_NAME='约束名';

Note: Adding constraints must be before inserting data. You cannot add constraints after inserting data!

• Non-null constraint (NOT NULL)

Requires that attributes cannot be empty, and inserting null values ​​is not allowed.

• Unique constraint (UNIQUE)

Requires attributes to be unique values ​​and does not allow identical data to appear.

• Primary key constraint (PRIMARY KEY)

The target attribute is required to be both non-empty and unique.

• Foreign key constraint (FOREIGN KEY)

corresponds to the primary key constraint. When inserting a record, the associated table (main table) must be inserted first ) to insert the associated table (slave table). The data in the slave table uniquely corresponds to the data in the master table.

• CHECK constraints

Constraints that limit the range of values ​​in a column

• DEFAULT constraints

It is used to set the default value in the column if other values ​​are not specified.

Recommended tutorial: "Oracle Video Tutorial"

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