Oracle Entry fourth day (bottom)--constraints

I. Overview

　　1. Classification

Table-level constraints are mainly divided into the following types:　　

　　　　Not NULL

　　　　UNIQUE

　　　　PRIMARY KEY

　　　　FOREIGN KEY

　　　　CHECK

　　2. Precautions

If you do not specify a constraint name, Oracle server automatically specifies the constraint name in the SYS_CN format

Create and modify constraints: After the table is built

Constraints can be defined at the table-level or column-level

Constraints can be viewed from the data dictionary view

　　3. How to create a constraint

　　　　 Column level: Columns [CONSTRAINT constraint_name] Constraint_type,

　　　　 table level :column,... [CONSTRAINT constraint_name] constraint_type (column, ...),

Second, non-null constraint--not NULL

Add directly when you define the table DDL:

CREATE TABLE Emp3 (       number (notNULL       ,VARCHAR2( Ten CONSTRAINT  not NULL )

　1. Note the wording of the word constraint ;

2. You can use a custom naming constraint (usually in the form of table name _ column name _ constraint name), and if omitted, the system name is used.

　　

third, the only constraint--unique

　　Note the following two ways to add constraints:

CREATE TABLE Emp4 (       number (UNIQUE,       VARCHAR2( ),       CONSTRAINTUNIQUE(name))

　　　1. A null value is allowed in the unique constraint

2. Allow multiple null values to appear

　　　We recommend that you manage constraints by naming them in a constrained way

Iv. PRIMARY KEY constraint--primary key

　　The primary KEY constraint is automatically owned by the definition of the primary key: the only non-null constraint　

CREATE TABLE EMP5 (       number (a),       VARCHAR2(ten),        CONSTRAINT PRIMARY KEY (ID))

　　The primary key is used to uniquely determine a record

Five, FOREIGN KEY constraint--foreign key

　　Refer to the primary key of the other column, called the Foreign key (the value of the foreign key must exist in the reference column)

CREATE TABLE emp6 (       number (a),       VARCHAR2(ten),        CONSTRAINT FOREIGN KEY REFERENCES EMP5 (ID))

　　Foreign key options available:

　　　　FOREIGN KEY: Specify columns in a child table at the table level

REFERENCES: Columns that are marked in the parent table

On delete CASCADE (cascade delete): When a column in the parent table is deleted, the corresponding column in the child table is also deleted

On DELETE SET null (cascade NULL): The corresponding column in the child table is empty

Example:

CREATE TABLE emp6 (       number (a),       VARCHAR2(ten),        CONSTRAINTFOREIGNKEYREFERENCESonDELETE CASCADE )

vi. checking and restricting--check

　　Constraints that define the conditions that must be met

CREATE TABLE EMP7 (       number (a),       VARCHAR2(ten),        CONSTRAINTCHECK> <  ))

vii. Constraint Management

　　are managed based on ALTER TABLE!

　　1. Adding constraints

ALTER TABLE     Table  ADD [CONSTRAINT CONSTRAINT] type (column);

　　Example:

ALTER TABLE ADD (CONSTRAINTPRIMARYKEY(name))

　Adding a NOT NULL constraint requires the use of the Modify keyword:

Alter Table varchar2 (notnull);

　　2. Delete a constraint

ALTER TABLE       EmployeesDROPCONSTRAINT  emp_manager_fk;

　　So the custom naming constraints are easy to manage.

　　3. Invalid constraint

ALTER TABLE CONSTRAINT emp_emp_id_pk;             

　　4. Activating an invalid constraint

ALTER TABLE CONSTRAINT emp_emp_id_pk;             

　5. Query constraints

SELECT     constraint_name, Constraint_type,    search_conditionfrom    user_ ConstraintsWHERE    ='EMPLOYEES';

　　6. Columns for query constraints

　　　　through the Data dictionary view: User_cons_columns, In fact, if the constraint name has a specification can be seen directly!

SELECT     constraint_name, column_namefrom    user_cons_columnsWHERE    ='EMPLOYEES';

Oracle Entry fourth day (bottom)--constraints