Column-level constraint conditions

DB2 constraint constraints

In addition to specifying a data type for each column of data in a two-dimensional table in a relational database, it is sometimes necessary to specify constraints to limit what data the column can store. There are five main constraints (Constraint) in relational databases: non-null, unique, primary key, foreign key, check.

There are two levels of constraints: column level and table level. If a constraint is limited to a column, it is a column-level constraint, and if a constraint is related to more than one field, you need to define a table-level constraint.

3.12.1 non-null constraint

A non-null (NOT NULL) constraint indicates that null values are not allowed in a field, which specifies that the field uses a non-null constraint, which is a column-level constraint. It is defined in the following way:

Field name data type not NULL

3.12.2 UNIQUE Constraint

Repeating values are not allowed in a field, which specifies that the field uses a unique constraint, and that it is also a column-level constraint. It is important to note that, starting with DB2 V9, the unique constraint also requires non-null, that is, a field is defined as unique, neither duplicate values nor null values can occur in the column. It is defined in the following way:

Field name data type UNIQUE

3.12.3 Main code constraints

The main code (Primary key) constraint, also known as the PRIMARY KEY constraint, is a combination of one or more fields, and the relational database requires that in one table, the exact same two rows cannot appear, usually the primary key being the field or combination of fields that can be used to distinguish between different records. In a real project, each table will often define a number field as the main code.

The main code requirement satisfies both non-null and unique conditions, and if the master code is composed of multiple field combinations, no null values can appear in each field, and the combinations of these fields cannot be duplicated.

If a single field is used as the main code, it can define either a column-level constraint or a table-level constraint. If you are working with multiple field groups as a primary code, you must define a table-level constraint.

Column-level master codes are defined in the following ways:

Field name data type not NULL PRIMARY KEY

It is important to note that the field must be specified as non-empty before the master code is defined.

Table-level master codes are defined in the following ways:

PRIMARY KEY (Field list)

3.12.4 External code constraint

The outer code (Foreign key) constraint, also known as a foreign key constraint, is usually defined as an association between two tables. The associated two tables, one as the main table, one from the table, one or more fields from the table to reference the corresponding fields in the main table, that is, from the table in these fields can only appear in the main table of the value (or null), can not appear other values do not appear. The following is an example of the creation of a foreign code.

Example 3?19 external code constraints.

Set up two tables, one for the Department, including two fields for number (DeptNo) and department name (Deptname), one for students table Student, including student number (Stuno), name (name), Gender (sex) and Department (DeptNo), The student table of the Department of the characters wrongly written section to the Reference system table, while the department table in a row record deletion, automatically delete all students in the Department of the student table.

CREATE TABLE Department (DeptNo CHAR (TEN) NOT null PRIMARY KEY, Deptname VARCHAR (a) NOT null) CREATE TABLE Student (Stu No CHAR (one) not null PRIMARY key, Name VARCHAR (8) NOT NULL, Sex CHAR (2), DeptNo char (Ten), FOREIGN KEY (DeptNo) REFERENCES D Epartment (DeptNo) on DELETE CASCADE)

In the last sentence created from table Student, the on delete CASCADE clause refers to the fact that when data in the primary table is deleted, the corresponding data in the table is cascade deleted.

This can also be written on delete set NULL, which indicates that when the primary table data is deleted, the corresponding data from the table is set to a null value, and the other wording is described in the information center.

about the creation of external code, there are three points to note:

First, the main table must be defined, and then defined from the table;

The second is that the field referenced from the table must be either a master code or a candidate code (a field with a unique constraint) in the primary table;

Third, the data type of the corresponding field from the table must be the same as in the primary table, but the field names do not require the same.

3.12.5 CHECK Constraints

If a field is required to take a value within a range, a check constraint is required, which can be a column-level constraint, with the syntax:

Field name data type CHECK (constraint)

It can also be a table-level constraint with the following syntax:

CHECK (Constraint)

3.12.6 Other constraints

In addition to the constraints of the five standard SQL definitions above, some constraints have been extended in DB2 to compare commonly used default values and identity columns.

The default value, also called the default value, is used to specify the default value for a field, and when the data is inserted without a value, it is populated with this default value, which is a column-level constraint with the following syntax:

Field name data type Default < default value >

Identity column, which defines a constraint on a numeric field that can be used to generate a value that automatically grows according to a certain rule when inserting a new row of records.

Identity columns can be generated in two ways: GENERATED always and GENERATED by DEFAULT, and the differences between the two approaches are described in detail below.

1. GENERATED always

This generation is always generated by DB2 and cannot be supplied directly to the application or SQL statement.

Example 3?20 GENERATED always generates an identity column.

The following example shows how the identity column generated by GENERATED always is used. Before running the following example, you should first turn off the auto-commit option (DB2 +c).

CREATE TABLE inventory1 (PartNo INTEGER GENERATED always as IDENTITY (START with the INCREMENT by 1), Description CHAR (2 0)); INSERT into Inventory1 VALUES (DEFAULT, ' door '); ---> Success, insert 100,door INSERT INTO Inventory1 (description) VALUES (' hinge '); ---> succeeded, insert 101,hinge insert INTO Inventory1 VALUES (102, ' window '); ---> failed, PartNo cannot insert value COMMIT; INSERT into Inventory1 (description) VALUES (' Lock '); ---> Success, inserted 102,lock ROLLBACK; ---> Canceled the previous action INSERT into Inventory1 (description) VALUES (' frame '); ---> Success, insert 103,frame COMMIT; SELECT * from Inventory1; Door 101 Hinge 103 Frame

2. GENERATED by DEFAULT

In this way, if the user does not provide a value for the identity column, DB2 will automatically generate a value, and if the user provides a value for the identity column, DB2 will no longer generate the value, but instead use the user-supplied value directly. In this way, the generated values are not guaranteed to be unique.

Example 3?21 GENERATED by DEFAULT for the identity column generated.

The following example shows how an identity column generated through GENERATED by DEFAULT is used. Before running the following example, it is also necessary to turn off the auto-commit option First (DB2 +c).

CREATE TABLE Inventory2 (PartNo INTEGER GENERATED by DEFAULT as IDENTITY (START with +, INCREMENT by 1), Description CH AR (20)); INSERT into Inventory2 VALUES (DEFAULT, ' door '); ---> Success, insert 100,door INSERT INTO Inventory2 (description) VALUES (' hinge '); ---> succeeded, insert 101,hinge insert INTO Inventory2 VALUES (102, ' window '); ---> Success, insert 102,window COMMIT; INSERT into Inventory2 (description) VALUES (102, ' Lock '); ---> Success, insert 102,lock INSERT INTO Inventory2 (description) VALUES (' Lock '); ---> Success, inserted 102,lock ROLLBACK; ---> Canceled the previous action INSERT into Inventory2 (description) VALUES (' frame '); ---> Success, insert 103,frame COMMIT; SELECT * from Inventory2; Door 101 Hinge 102 Window 103 frame

You can use the identity_val_local () function to get the generated value of the currently generated column, which is primarily used to insert data from the table to the corresponding column, for example:

INSERT into parent_table (pk_id, ...) VALUES (DEFAULT, ...); INSERT into Child1_table (..., fk_id,...) VALUES (....., identity_val_local (),...); INSERT into Child2_table (..., fk_id,...) VALUES (....., identity_val_local (),...);

Where parent_table is the primary table, PK_ID is the primary code and is the identity column. Child1_table and child2_table are reference to the main table from the table, where fk_id is the outer code, referring to the main table in the master code. Since the value of the outer code appears in the main table referenced by the main code, and the primary code in the primary table is generated automatically by the identity column, the identity_val_lacal () function is required to know the current generated value.

Column-level constraint conditions