Constraints (constraint)

1. Why use constraints:

The constraints are also called integrity constraints. When you perform DML operations on the data in the table, the system verifies whether the data violates the constraints. if the DML operation is violated, it will fail. constraints can be applied to one or several columns in a table, and to the entire table or between several tables.

Type of constraints: non-null (not null), unique (unique), primary key (Primary Key), foreign key (foreign key), check ).

Not null can only be applied to columns. if you want to apply it to a table, that table is useless. the constraints are named like other database objects and can be customized by the user. if this parameter is not specified, the system will generate it by default. the format is sys_cxxx. xxx represents random numbers.

 

2. Create constraints Syntax:

1. Specify constraints when creating a table:

Create Table [schema.] table_name (

Column_name datatype [Default expr] [column_constraint], [,...],

[Table_constraint]

);

 

Column_constraint = [constraint constraint_name] constraint_tyep;

Table_constraint = [constraint constraint_name] constraint_tyep (column,...)];

2. Specify constraints after the table is created:

Alter table table_name add [constraint constraint_name] constraint_type (column ,...);

 

3. non-null constraint (not null ):

Constraint and name specified during table Creation: Create Table Arwen (ename char (10), Eno int constraint arwen_eno_nn not null ).

When creating a table, the constraint does not specify the name:Create Table Arwen (ename char (10), Eno int not null ).

Constraints after table creation:Create Table Arwen (ename char (10), Eno INT );

Alter table Arwen modify (ENO int not null). --- only not null, modify, and add

Use it after creating the constraint:

Insert into Arwen (ename) values ('good'); an error occurs, prompting that null cannot be inserted.

 

4. unique constraint (unique ):

Constraints during table creation and names specified:Create Table Arwen (ename char (10), Eno int constraint arwen_eno_uk unique) or

Create Table Arwen (ENO int, ename char (10), constraint arwen_eno_uk unique (ENO ))

When creating a table, the constraint does not specify the name:Create Table Arwen (ename char (10), Eno int unique ).

Constraints after table creation:Create Table Arwen (ename char (10), Eno INT );

Alter table Arwen add unique (ENO) or

Alter table Arwen add constraint arwen_eno_un unique (ENO );

Use it after creating the constraint:

Insert into Arwen values ('good', 12); runs twice in a row, and the second error occurs.

If insert into Arwen values ('good', null); no error occurs no matter how many times

 

5. check ):

Constraints during table creation and names specified:Create Table Arwen (ename char (10), Eno int, constraint arwen_eno_check check (ENO> 10 ))

When creating a table, the constraint does not specify a name.: Create Table Arwen (ename char (10), Eno int, check (ENO> 10 ))

Constraints after table creation:Create Table Arwen (ename char (10), Eno INT );

Alter table Arwen add check (ENO> 10) or

Alter table Arwen add constraint arwen_eno_check check (ENO> 10)

Use it after creating the constraint:

Insert into Arwen values ('good', 3); error.

If insert into Arwen values ('good', 13 );

 

6. Primary Key (primary ):

Constraints during table creation and names specified:Create Table Arwen (ename char (10), Eno int, constraint arwen_eno_pk primary key (ENO ))

When creating a table, the constraint does not specify the name:Create Table Arwen (ename char (10), Eno int primary key)

Constraints after table creation:Create Table Arwen (ename char (10), Eno INT );

Alter table Arwen add primary key (ENO) or

Alter table Arwen add constraint arwen_eno_pk primary key (ENO)

Use it after creating the constraint:

Insert into Arwen values ('good', 12); runs twice in a row, and the second error occurs.

If insert into Arwen values ('good', null); is executed twice consecutively, the second error occurs.

 

7. Foreign key (foreign key ):

Suppose there are tables Arwen (ename, ENO) and Info (Eno, location ). in table info, Eno is the primary key. in the Arwen table, Eno is a foreign key. when data is inserted into Arwen. the Eno value must be the Eno value in the info table. this is a bit like the enumeration type in other programming languages. you can only use one of them. however, the difference here is that you can insert null to the Eno column in Arwen.

Note: Eno in Arwen can also be the primary key, that is, a column can be both the primary key and the foreign key.

Create Table Info (location char (10), Eno int primary key );

Constraint and name specified during table Creation: Create Table Arwen (ename char (10), Eno int, constraint arwen_eno_fk foreign key (ENO) References (Info (ENO ))

When creating a table, the constraint does not specify the name:Create Table Arwen (ename char (10), Eno int foreign keyreferences (Info (ENO ))

Constraints after table creation:Create Table Arwen (ename char (10), Eno INT );

Alter table Arwen addforeign key (ENO) References (Info (ENO) or

Alter table Arwen add constraint arwen_eno_fk foreign key (ENO) References (Info (ENO ))

Use it after creating the constraint:

Insert into info values ('hunance', 12 );

Insert into Arwen values ('good', 13); -- an error occurs here. It is correct if you change 13 to 12.

Supplement: If the Eno In the table Arwen is both the primary key and the foreign key, the table is created in this way.

Create Table Arwen (ename char (10), Eno int primary key, foreign key (ENO) References (Info (ENO ))

 

8. Disable and enable constraints:

Syntax:

Alter table table_name

Disable | enable constraint constraint_name [cascade];

Constraint_name is used here. If you do not manually name it, the system will generate it by default. You need to find it in the data dictionary to know what it is.

For example, the constraints on the search table Arwen:

Select constraint_name, constraint_type from user_constraints where table_name = 'arwen'; -- Remember to enclose the quotation marks, and Arwen must be capitalized.

HereCascade usage: If it is written, it means that you disable the primary key constraint and also disable the corresponding foreign key constraint.However, when the primary key constraint is restarted, the foreign key constraint is not automatically restarted.

If you do not enter cascade. When you disable the primary key constraint, an error occurs if it has the corresponding foreign key constraint. You must delete or disable the corresponding foreign key constraint before you can disable the primary key constraint.

 

9. Modify and delete the constraint name

If you didn't name the constraint at first, you have to query the data dictionary when disabling and starting the constraint. This is a lot of trouble. this will make you regret being lazy and rename it. but fortunately, there is a way to change the name, you can make up for it.

Syntax:

Alter table table_name rename constraint old_name to new_name;

Assume that the name generated by a constraint system in the preceding table Arwen is sys_c001234.

You want to change it to arwen_ename_pk.

Alter table Arwen rename constraint sys_c001234 to arwen_ename_pk

 

Syntax:

Alter table table_name

Drop constraint constraint_name [cascade];

Example: alter table Arwen drop constraint arwen_ename_pk;

If a primary key constraint exists and the corresponding foreign key constraint exists, an error occurs when you delete the primary key constraint as described above. Unless you delete the foreign key constraint first, you can also use the following method.

For example, when the alter table Arwen drop constraint arwen_ename_pk cascade deletes the primary key constraint, the foreign key constraint is also deleted.

 

Supplement: if the permission is not set, the deletion constraint is used. If a foreign key constraint exists, the cascade constraint must be used.

For example, to delete a table Arwen:

Drop table Arwen cascade constraint; -- delete the table Arwen and delete the corresponding foreign key constraint.