Db2 简明教程
DB2 - Constraints
本章描述了数据库中的各种约束。
This chapter describes various constraints in the database.
Introduction
为了强制执行数据库的完整性， 定义了一组规则，称为约束。这些约束允许或禁止列中的值。
To enforce database integrity, a set of rules is defined, called constraints. The constraints either permit or prohibit the values in the columns.
在实时数据库活动中， 应该根据某些限制添加数据。例如， 在销售数据库中， 销售 ID 或交易 ID 应当唯一。约束类型有：
In a Real time database activities, the data should be added with certain restrictions. For example, in a sales database, sales-id or transaction-id should be unique. The constraints types are:
-
NOT NULL
-
Unique
-
Primary key
-
Foreign Key
-
Check
-
Informational
约束仅与表相关。它们仅应用于特定表。它们在创建表时定义和应用于表。
Constraints are only associated with tables. They are applied to only particular tables. They are defined and applied to the table at the time of table creation.
Explanation of each constraint:
NOT NULL
这是禁止表中一个或多个列的空值规则。
It is a rule to prohibit null values from one or more columns within the table.
Syntax:
Syntax:
db2 create table <table_name>(col_name col_type not null,..)Example : [要创建一个销售表，其中有四列（id、itemname、qty、price），在此向所有列添加“非空”约束以避免在表中形成任何空单元。]
Example: [To create a sales table, with four columns (id, itemname, qty, price) in this adding “not null” constraints to all columns to avoid forming any null cell in the table.]
db2 create table shopper.sales(id bigint not null, itemname
varchar(40) not null, qty int not null,price double not null)Inserting NOT NULL values into table
你可以按如下方式在表中插入值：
You can insert values in the table as shown below:
Example: [错误查询]
Example: [ERRORoneous Query]
db2 insert into shopper.sales(id,itemname,qty)
values(1,'raagi',12)Output: [正确查询]
Output: [Correct query]
DB21034E The command was processed as an SQL statement because
it was not a
valid Command Line Processor command. During SQL processing
it returned:
SQL0407N Assignment of a NULL value to a NOT NULL column
"TBSPACEID=5,
TABLEID=4, COLNO=3" is not allowed. SQLSTATE=23502Example: [正确查询]
Example: [Correct query]
db2 insert into shopper.sales(id,itemname,qty,price)
values(1,'raagi',12, 120.00)
db2 insert into shopper.sales(id,itemname,qty,price)
values(1,'raagi',12, 120.00)Output:
Output:
DB20000I The SQL command completed successfully.Unique constraints
使用这些约束，你可以唯一地设置列的值。为此， 在创建表时使用“非空”约束声明唯一约束。
Using these constraints, you can set values of columns uniquely. For this, the unique constraints are declared with “not null” constraint at the time of creating table.
Syntax:
Syntax:
db2 create table <tab_name>(<col> <col_type> not null unique, ...)Example:
Example:
db2 create table shopper.sales1(id bigint not null unique,
itemname varchar(40) not null, qty int not null,price
double not null)Inserting the values into table
Example: 插入具有唯一 ID 1、2、3 和 4 的四行。
Example: To insert four different rows with unique ids as 1, 2, 3 and 4.
db2 insert into shopper.sales1(id, itemname, qty, price)
values(1, 'sweet', 100, 89)
db2 insert into shopper.sales1(id, itemname, qty, price)
values(2, 'choco', 50, 60)
db2 insert into shopper.sales1(id, itemname, qty, price)
values(3, 'butter', 30, 40)
db2 insert into shopper.sales1(id, itemname, qty, price)
values(4, 'milk', 1000, 12)Example: 插入一个具有“id”值 3 的新行
Example: To insert a new row with “id” value 3
db2 insert into shopper.sales1(id, itemname, qty, price)
values(3, 'cheese', 60, 80)Output : 当你尝试插入一行已存在的 id 值时， 它将显示此结果：
Output: when you try to insert a new row with existed id value it will show this result:
DB21034E The command was processed as an SQL statement
because it was not a
valid Command Line Processor command. During
SQL processing it returned:
SQL0803N One or more values in the INSERT statement,
UPDATE statement, or foreign key update caused by a
DELETE statement are not valid because the primary key,
unique constraint or unique index identified by "1" constrains
table "SHOPPER.SALES1" from having duplicate values for the
index key. SQLSTATE=23505Primary key
与唯一约束类似，你可以使用“主键”和“外键”约束来声明多个表之间的关系。
Similar to the unique constraints, you can use a “primary key” and a “foreign key” constraint to declare relationships between multiple tables.
Syntax:
Syntax:
db2 create table <tab_name>( ,.., primary
key ())Example : 创建“salesboys”表，其中“sid”为主键
Example: To create ‘salesboys’ table with “sid” as a primary key
db2 create table shopper.salesboys(sid int not null, name
varchar(40) not null, salary double not null, constraint
pk_boy_id primary key (sid))Foreign key
外键是一张表中的一组列，必须匹配另一张表中一行的至少一个主键。它是一种引用约束或引用完整性约束。它是一条关于一个或多个表中多列值的逻辑规则。它使表之间存在必需的关系。
A foreign key is a set of columns in a table which are required to match at least one primary key of a row in another table. It is a referential constraint or referential integrity constraint. It is a logical rule about values in multiple columns in one or more tables. It enables required relationship between the tables.
先前， 你创建了一张名为“shopper.salesboys”的表。对于此表，主键为“sid”。现在你正在创建一张具有不同模式的销售人员个人详细信息的新表，名为“employee”和表名为“salesboys”。在这种情况下，“sid”是外键。
Earlier, you created a table named “shopper.salesboys” . For this table, the primary key is “sid”. Now you are creating a new table that has sales boy’s personal details with different schema named “employee” and table named “salesboys”. In this case, “sid” is the foreign key.
Syntax:
Syntax:
db2 create table <tab_name>(<col> <col_type>,constraint
<const_name> foreign key (<col_name>)
reference <ref_table> (<ref_col>)Example : [要创建一个名为“salesboys”的表，其中包含外键列“sid”]
Example: [To create a table named ‘salesboys’ with foreign key column ‘sid’]
db2 create table employee.salesboys(
sid int,
name varchar(30) not null,
phone int not null,
constraint fk_boy_id
foreign key (sid)
references shopper.salesboys (sid)
on delete restrict
)Example : [将值插入到主键表“shopper.salesboys”]
Example: [Inserting values into primary key table “shopper.salesboys”]
db2 insert into shopper.salesboys values(100,'raju',20000.00),
(101,'kiran',15000.00),
(102,'radha',10000.00),
(103,'wali',20000.00),
(104,'rayan',15000.00)Example : [将值插入外键表 “employee.salesboys” [无错误]]
Example: [Inserting values into foreign key table “employee.salesboys” [without error]]
db2 insert into employee.salesboys values(100,'raju',98998976),
(101,'kiran',98911176),
(102,'radha',943245176),
(103,'wali',89857330),
(104,'rayan',89851130)如果您输入了一个未知的数字（未存储在 “shopper.salesboys” 表中），这将向您显示 SQL 错误。
If you entered an unknown number, which is not stored in “shopper.salesboys” table, it will show you SQL error.
Example : [错误执行]
Example: [error execution]
db2 insert into employee.salesboys values(105,'rayan',89851130)Output:
Output:
DB21034E The command was processed as an SQL statement because it
was not a valid Command Line Processor command. During SQL
processing it returned: SQL0530N The insert or update value of
the FOREIGN KEY "EMPLOYEE.SALESBOYS.FK_BOY_ID" is not equal to any
value of the parent key of the parent table. SQLSTATE=23503Checking constraint
您需要使用此约束来为表中的特定列添加条件限制。
You need to use this constraint to add conditional restrictions for a specific column in a table.
Syntax:
Syntax:
db2 create table
(
primary key (),
constraint check (condition or condition)
)Example : [使用约束值创建 emp1 表]
Example: [To create emp1 table with constraints values]
db2 create table empl
(id smallint not null,
name varchar(9),
dept smallint check (dept between 10 and 100),
job char(5) check (job in ('sales', 'mgr', 'clerk')),
hiredate date,
salary decimal(7,2),
comm decimal(7,2),
primary key (id),
constraint yearsal check (year(hiredate) > 1986 or salary > 40500)
)