Reprint: Oracle Add constraint Disable delay

Add NOT NULL constraint

ALTER TABLE Tb_name

Modity column_name constraint nt_name not null;

It is important to note that adding a NOT NULL constraint does not make the ADD constraint statement, but instead uses modify.

Add FOREIGN KEY constraint

ALTER TABLE TB_NAME1

Add constraint fk_name

Foreign key (column_name1) references tb_name2 (column_name2);

When adding a foreign key, if you add the keyword on DELETE cascade at the end, all rows matching the child table will be deleted when a row of records is deleted in the parent table. If you add the keyword on delete set null the child table foreign key will be set to a null value when the parent table deletes a row of records. If you add the keyword on Delete no action then the child table foreign key does not do any processing when the parent table deletes a row of records.

Add PRIMARY KEY constraint

ALTER TABLE Tb_name

Add constraint uk_name primary KEY (column_name);

Add a CHECK Constraint

ALTER TABLE Tb_name

Add constraint ck_name check (column_condition);

In general, when you add a constraint, all existing rows in the table must meet this constraint. However, if you can disable a constraint when you initially add a constraint and you can make the constraint apply only to newly added data by specifying the Enable Novalidate option.

Add a UNIQUE Constraint

ALTER TABLE Tb_name

Add constraint uq_name unique (column_name);

Delete Constraint

ALTER TABLE Tb_name

DROP constraint constraint name;

When you delete a primary KEY constraint, you must add the Cascade keyword if there is a primary foreign key relationship.

ALTER TABLE Tb_name

Drop constraint primary key cascade;

When you delete a constraint, the index corresponding to the constraint is deleted by default, and if you want to keep the index, use the Keep index keyword.

ALTER TABLE Tb_name

Drop primary key Keep index;

Rename constraint

ALTER TABLE Tb_name

Rename constraint constraint name 1 to constraint name 2;

You can use it to rename some of the constraints that were originally unnamed to be automatically named by Oracle as the constraint name you want.

disabling constraints

ALTER TABLE Tb_name

Disable constraint constraint name ;

When you disable a primary key, if no cascade keyword can cause a foreign key reference inconsistency and error, you should use the CASCADE clause disable the primary key so that the primary key can be disable with the associated foreign key.

ALTER TABLE Tb_name

Disable primary key cascade;

For example, in the database system large-scale loading of data, for the efficiency of the system has to sacrifice the consistency of data to close some constraints, and even remove some constraints to prohibit the primary table primary key, but also prohibit the foreign key dependent on this primary key is forbidden.

The index is dropped when the constraint is disabled (primary key + unique), and the index is rebuilt when the constraint is enabled, which is extremely expensive for large tables. Therefore, you can disable the constraint without dropping the index, using the keyword Keep, such as:

ALTER TABLE Tb_name

Disable constraint constraint name keep index;

When you delete a constraint, you can use the keyword validate to verify that the existing data conforms to the constraint. It is important to note that DML operations on the table cannot be followed, such as:

ALTER TABLE Tb_name

Disable validate constraint constraint name ;.

You can also use the keyword novalidate to not validate existing data (default, omit is not verified), such as:

ALTER TABLE Tb_name

Disable novalidate constraint constraint name ;

Enable constraints

ALTER TABLE Tb_name

Enable constraint constraint name ;

If cascade is used when the primary KEY constraint is turned off, using enable does not open the primary KEY constraint with the FOREIGN KEY constraint at the same time, only the primary KEY constraint, and then the foreign KEY constraint is opened with the Enable CONSTRAINT clause.

When you enable constraints, you can set whether to validate existing data, add keywords novalidate after enable, or validate existing data, validate validate existing data (default, omit is validation). Such as:

ALTER TABLE Tb_name

Enable novalidate constraint constraint name ;

Constraint delay

Constraint delay is the validation when a transaction commits, and the default is no delay.

Open constraint delay: Set constraint constraint name deferred;

Close constraint delay: Set constraint constraint name immediate;

Disclaimer: This note is reproduced in the Oracle Database 11g SQL Development Guide, only for personal learning, if infringed upon your copyright please inform!

Reprint: Oracle Add constraint Disable delay