Oracle Study Notes 10

This note is used to learn constraints, which are everywhere in the table. For example, data in a table cannot be empty or the table id must be set as the primary key.

Constraint type:

Primary key: The primary key represents a unique identifier in the table and cannot be empty.

UNIQUE constraint: Only one primary key constraint can be created in a table. Other columns do not have duplicate values.

CHECK constraints: CHECK whether the content of a column is legal. For example, the salary is between 1500 and 3000.

Non-NULL constraint (not null): The content cannot be empty.

Foreign key constraint (foreign key): Constraints between two tables.

The constraint also has a name. If the constraint name is not specified, the system will automatically recreate the constraint name for us. The format is similar to sys_cn. You can also create your own

Name of the constraint, in the format of constraint table name_column name_constraint name constraint. In the format of "Table name_column name_constraint name,

It is also intended to achieve a known effect. Definition method: the column constraint must be defined after the column, and the table-level constraint can be defined separately without following the column.

Constraints: column-level constraints and table-level constraints. Column-level constraints can only apply to one column. Table-level constraints can apply to multiple columns or one column.

You can create or modify constraints. Create constraints when creating a table. Modify the constraints after the table is created.

Basic syntax format:

Column-level constraint definition:

Create table table_name (

Data Type of Column 1 [constraint | constraint name constraint],

Data Type of column name 2 [constraint | constraint name constraint]

)

Table-level constraint definition:

Create table table_name (

The data type of column 1,

Data Type of column name 2,

Constraint name constraints (column names)

)

Not null Constraint

Non-null constraints can only be defined in columns. A non-empty constraint indicates that the content of a field cannot be blank. That is, the inserted data must contain content.

Is not allowed to be null.

Example: Create Table emp2, which contains three columns: id, name, and salary. The id column must not be empty.

Create table emp2 (

Idnumber (9) constraintemp2_id_nn not null,

Namevarchar2 (20) not null,

Salarynumber (10, 2)

)

Constraintemp2_id_nn column-level constraints. It indicates a name for the constraint. Use the constraint keyword for definition.

For example, to test the data, try to add the data whose name is null to emp2.

Insert into emp2

Valuees (1001, null, 2000)

 

ORA-01400: cannot insert NULL ("SCOTT". "EMP2". "NAME ")

If a null value is inserted after a non-null constraint is set, the preceding error occurs. The column name cannot insert a null value.

UNIQUE Constraint)

The column value is unique and repeated values are not allowed in other columns of the table. However, multiple null values are allowed in the table.

Example: Create Table emp3

Create table emp3

(

Idnumber (8) constraintemp3_id_uk unique,

Namevarchar2 (20) constraintemp3_name_nn not null,

Salarynumber (10, 2 ),

Emailvarchar2 (20 ),

-- Table-level constraints

Constraintemp3_email_uk unique (email)

)

After the following statement is executed twice:

Insert into emp3

Values (100, 'hangsan1', 2000, 'zhang @ 128.com ')

 

ORA-00001: violation of the unique constraint condition (SCOTT. EMP3_ID_UK), prompting a violation of the unique constraint. However, if you insert two pieces of data that are worthwhile for the Set (100, 'zhang', 2000, null) and (1001, 'san', 3000, null, although the email columns in the above two worthy sets are null values, they can still be inserted successfully. The column with unique constraints is set. null is not considered to be repeated.

PRIMARY KEY)

It is generally used on the table id. The default content cannot be blank.

Create table emp4

(

Idnumber (8) constraintemp4_id_pk primary key,

Namevarchar2 (20) constraintemp4_name_nn not null,

Salarynumber (10, 2 ),

Emailvarchar2 (20 ),

-- Table-level constraints

Constraintemp4_email_ukunique (email)

)

Insert into emp4values (null, 'hangsan ', 100, '20150901 ')

ORA-01400: cannot insert NULL ("SCOTT". "EMP4". "ID ")

Insert into emp4values (1001, 'hangsan ', 100, '20150901 ')

ORA-00001: violation of unique constraints (SCOTT. EMP4_ID_PK)

When a record with id 1001 already exists in the table, data cannot be inserted. The primary key constraint is set. The value cannot be empty or repeated.

Foreign key constraint (foreign key)

The foreign key constraint applies to two tables. The foreign key set in the word table must be set as the primary key in the main table. Then, delete the records of the child table before deleting the records of the parent table.

Create a foreign key constraint statement:

Constraint name foreign key (sub-table column name) references parent table name (parent table column name ). The name of the subtable and parent table must be the same.

Create table emp8

(

Idnumber (8 ),

Name varchar2 (20) constraintemp8_name_nn not null,

Salary number (1500) check (salary> 30000 and salary <),

Email varchar2 (20 ),

Department_id number (10 ),

-- Table-level constraints

Constraintemp8_email_uk unique (email ),

Constraintemp8_id_pk primary key (id ),

Constraintemp8_dept_id_fk foreignkey (department_id) references

Parameters (department_id)

)

Insert into emp6

Values (1001, 'A', null, 2000,200 0)

ORA-02291: the complete constraint condition (SCOTT. EMP6_DEPT_ID_FK) is violated-the parent keyword is not found. The above deptment_id = 2000 does not exist in the deptments table, and the above error occurs.

After the foreign key constraint is set, the columns in the Word Table are restricted by the parent table. If it is a value not in the parent table, data cannot be inserted successfully.

 

-On delete cascade: 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 ): columns in the subtable are empty.

 

Check Constraints

Use the check constraint to check whether the content inserted in a class is legal. For example, the salary ranges from 1500 to 5000.

Create table emp8

(

Id number (8 ),

Name varchar2 (20) constraint emp8_name_nn not null,

Salary number (1500) check (salary> 30000 and salary <),

Email varchar2 (20 ),

Department_id number (10 ),

-- Table-level constraints

Constraint emp8_email_ukunique (email ),

Constraint emp8_id_pkprimarykey (id ),

Constraint emp8_dept_id_fk foreign key (department_id)

References parameters (department_id)

)

SQL> insert into emp8

2 values (1001, 'hangsan', 2000, 'A', 30 );

1 row inserted

 

SQL> insert into emp8

2 values (1002, 'zhang ', 500, 'bb', 30 );

ORA-02290: violation of check constraints (SCOTT. SYS_C005496), the above error is prompted when the inserted data does not meet the salary range.

Modify Constraints

Modify Constraints

Use modify to add not null Constraints

Alter table emp5

Modify (salary number (10) not null)

Delete Constraints

Alter table emp5

Drop constraint emp5_name_nn (constraint name)

Add Constraints

Alter table emp5

Add constraint emp5_nameul unique (name );

 

Valid constraints and invalid constraints.

Alter table emp3

Enable constraint emp3 -- name-uk (constraint name)

 

Alter table emp3

Disable constraint emp3 -- name-uk (constraint name)