In Oracle 10 Gb, detailed description about the constraints used in tables and columns.

Original article address: http://www.cnblogs.com/chenkai/archive/2010/02/22/1671442.html. Click Open Link

Check Constraints

Code

-- Create Table 'create table order_status (ID integer constraint order_status_pk primary key, status varchar (120), last_modified date default sysdate ); -- add the check constraint alter table order_status add constraint order_status_ck check (status in ('beijing', 'shanghai', 'Taiwan ')); alter table order_status add constraint order_status_id_ck check (ID> 10); -- test data insert into order_status (ID, status) values (132, 'shanghai ');

Note:

(1) When creating a check constraint, all the rows in the table must meet the Add constraints. If a data condition already exists in the Add constraint does not meet, A ORA-02293 error is prompted:

Generally, we recommend that you clear the data in the original table before adding a constraint. The preceding error indicates that the constraint is disabled. this involves the "enable constraint" and "Disable constraint" operations. detailed description later. you can also specify the Enable novalidate option. so that the constraints only apply to newly added data. you don't have to worry about adding data before adding it. A ORA-02290 error is prompted if the data does not comply with the check constraints when inserting data. modify and insert data.

B: Not null Constraint

Code:

-- Add the not null constraint alter table order_status modify status constraint order_status_newref not null; alert table order_status modify last_modified not null; format: note that the Add constraint format is as follows: Alert table [Table name] modify [Table name] constraint [constraint unique identifier] Not NULL;

Note:

A1: When a constraint is added for last_modified, the system automatically assigns a name to identify the constraint. However, it is recommended that you manually use a meaningful name.

C: foreign key constraint of foreign key.

Code:

-- Create a foreign key constraint test table create global temporary table test_orderstatus (ID integer constraint order_statustest_pk primary key, status_id number (15), status varchar (120), last_modifieddate date default sysdate ); -- add an alter table test_orderstatus drop column status_id after deleting a column; alter table test_orderstatus add constraint order_status_modify_fk status_id reference order_status (ID );

Use the on Delete cascade clause for the foreign key. That is, when a row of records is deleted in the parent table, the row records associated with the foreign keys in the child table are automatically deleted.

-- With automatic cascade update alter table test_orderstatus drop column status_id; alter table test_orderstatus add constraint order_status_modify_fk status_id reference order_status (ID) on Delete cascade;

When a record of the parent table is deleted, the sub-Table record cannot be deleted. if all records associated with foreign keys in the Word Table are set to null, use the on Delete cascade null clause. also after the foreign key constraint. when deleting a parent table record. all data associated with the parent table through the current foreign key is set to null.

D: unique constraint

-- Add the unique constraint alter table order_status add constraint order_status_uq unique (Status );

E: Delete references)

-- Delete the unique constraint.
Alter table order_status
Drop constraint order_status_uq;

When you need to manually encode and delete constraints, it is critical to find the unique name of the constraint. Therefore, we recommend that you create meaningful constraints for easy identification.

Disable references)

-- Disable the constraint alter table order_statusadd constraint order_status_uq unique (Status) Disable during creation;

By default, the Add constraint is automatically enabled after being created. when creating a table, you can directly disable the direct tail and add a disable clause. If the constraint is disabled after the creation, use alter table ..... disable constraint clause

-- Disable constraints in use
Alert table order_status
Disable constraint order_status_uq;
E: restart references)

-- Enable the constraint alert table order_status enable constraint order_status_uq;

The constraint is automatically enabled after it is created by default. to enable constraints, note that all data in the current table must meet the constraints. otherwise it will pack ORA-02293 error. of course, you can only restrict the newly inserted data as needed. the original still exists. Use alter table ....... enable novalidate. the default value is enable constraint.

-- The current constraint alert table order_statusenable novalidate constraint order_status_uq is executed only for newly inserted data;

Shangmian we also talked about this issue when using the first check constraint above. when we create a check constraint, because there is still data in the table before the constraint is created. Some of the data does not meet the current constraint, but we do not want to delete it. the use of the Enable novalidate clause is a good choice. It is used to constrain the newly inserted data. the original data is retained.

G: latency constraint (deferred constraint)

Latency constraints are enforced when a transaction is committed. when adding constraints, you can use the deferrable clause to specify constraints as latency constraints. constraint 1. Once created, it cannot be changed to deferrable latency constraint. the only method is to delete the constraint, which can only be specified as a latency constraint during creation.

-- Currently, the only constraint created above is the latency constraint. First, delete the created unique constraint. -- specify the delayed alter table order_status drop constraint order_status_uq alert table order_status add constraint order_status_uq unique (status) deferrable initially deferred;

Note that when you add a constraint above, you can mark it as initially immediate or initially deferred.

Initially immediate indicates that each time you add data to or modify or delete data from a table. check the constraints. (this is the same as the default behavior of the constraint ). and initially deferred. this indicates that this constraint is checked only when the transaction is committed. the deferred. this constraint is checked only when the transaction is committed.

H: obtain information about the constrained system

You can query the user_constraints table to obtain all the current constraints on the system information. The following section describes the constraints on common information fields in the user_constraints table:

Owner -- constraint owner

Constraint_name -- constraint name

Constraint_type -- constraint type: C: indicates the check or not null constraint. p: primary key constraint. r: foreign key constraint. u: unique constraint. v: Check option constraint. o: readonly read-only constraint

Table -- name -- constraint definition for table name

Status _ the status of the constraint enable or disable is available or unavailable

Deferrable: whether the delay constraint value is: deferrable or not deferred.

Deferred: whether the delay value is immediate or deferred.

-- View all the constraints on the order_status table select * From user_constraints where table_name = 'order _ status'

Shangshu is a table constraint. You only need to query the user_cons_columns table for specific columns in the table.