Oracle Data Integrity (learning notes)

Data integrity Classification

1. Domain Integrity

Domain integrity, also known as column integrity, specifies whether a dataset is valid for a column and determines whether null values are allowed.

2. Entity integrity

Entity integrity can also be called row integrity, requiring each row in the table to have a unique identifier, primary key.

3. Referential integrity

Also called referential integrity.

Main differences between primary key and unique key:

(1) A data table can only create one primary KEY constraint, but several unique keys may be created.

(2) The primary key field value is not allowed to be empty, and the unique key can.

Same point:

Both do not allow duplicate values for the corresponding fields in the table, and the indexes are automatically generated when the primary key and unique key constraints are created.

Column-Level definitions:

A column-level definition defines a constraint while defining a column .

Table-Level definitions:

Defines a constraint after all columns have been defined.

To add a primary key when creating a table:

Sql> CREATE TABLE table_name (Field_name char (8) primary key);

To add a foreign key when creating a table:

Sql> CREATE TABLE table_name (Field_name char (8) References table_name2 (field_name2));

Where Table_name2 is the name of the table to be referenced, field_name2 is the corresponding primary key in Table_name2, and the Field_name and field_name2 data types must be exactly the same.

If you forget to add a constraint while building a table, you can use the ALTER TABLE command to add a constraint to the table. Note, however, that adding not NULL uses the Modify option to add primary key, unique, Foregin key, and check with the Add option.

Increase not NULL:

Sql> ALTER TABLE table_name modify FIELD_NAME NOT NULL;

Add Unique:

Sql> ALTER TABLE TABLE_NAME ADD constraint constraint_name unique (field_name);

Or:

Sql> ALTER TABLE table_name add unique (field_name);

Add primary key:

Sql> ALTER TABLE TABLE_NAME ADD CONSTRAINT constraint_name primary key (Field_name);

Or:

Sql> ALTER TABLE TABLE_NAME ADD PRIMARY key (key);

Add foreign Key:

Sql> ALTER TABLE TABLE_NAME ADD CONSTRAINT constraint_name foreign key (field_name1) references table_name2 (field_name 2);

Or:

Sql> ALTER TABLE TABLE_NAME add FOREIGN KEY (field_name1) references table_name2 (field_name2);

To delete a constraint:

Sql> ALTER TABLE table_name DROP CONSTRAINT constraint_name;

Or:

Sql> ALTER TABLE table_name drop unique (field_name); Sql>alter table table_name drop PRIMARY key;

When you delete a primary KEY constraint, you can produce an error because the two tables have a master-slave relationship, and you must add cascade when you delete the primary KEY constraint.

Sql> ALTER TABLE table_name DROP PRIMARY key cascade;

This article is from the "Flying Fish Technology" blog, please be sure to keep this source http://flyingfish.blog.51cto.com/9580339/1582961

Oracle Data Integrity (learning notes)