约束Constraints

• 02/16/2021
• • m
  • o

Delta 表支持标准 SQL 约束管理子句，以确保自动验证添加到表中的数据的质量和完整性。Delta tables support standard SQL constraint management clauses that ensure that the quality and integrity of data added to a table is automatically verified. 当违反约束时，Delta Lake 将引发 InvariantViolationException 以指示无法添加新数据。When a constraint is violated, Delta Lake throws an InvariantViolationException to signal that the new data can’t be added.

支持两种类型的约束：Two types of constraints are supported:

• NOT NULL：指示特定列中的值不能为 null。NOT NULL: indicates that values in specific columns cannot be null.
• CHECK：指示每个输入行的指定的布尔表达式必须为 true。CHECK: indicates that a specified Boolean expression must be true for each input row.

NOT NULL 约束NOT NULL constraint

在创建表时，在架构中指定 NOT NULL 约束，并使用 ALTER TABLE CHANGE COLUMN 命令删除 NOT NULL 约束。You specify NOT NULL constraints in the schema when you create a table and drop NOT NULL constraints using the ALTER TABLE CHANGE COLUMN command.

CREATE TABLE events(
  id LONG NOT NULL,
  date STRING NOT NULL,
  location STRING,
  description STRING
) USING DELTA;

ALTER TABLE events CHANGE COLUMN date DROP NOT NULL;

您可以 NOT NULL 使用命令将约束添加到 现有的增量表 ALTER TABLE CHANGE COLUMN SET NOT NULL 中。You can add NOT NULL constraints to an existing Delta table using the ALTER TABLE CHANGE COLUMN SET NOT NULL command.

CREATE TABLE events(
  id LONG,
  date STRING,
  location STRING,
  description STRING
) USING DELTA;

ALTER TABLE events CHANGE COLUMN id SET NOT NULL;

如果 NOT NULL 对嵌套在结构内的列指定约束，则父结构也会被约束为 not null。If you specify a NOT NULL constraint on a column nested within a struct, the parent struct is also constrained to not be null. 但是，嵌套在数组或映射类型中的列不接受 NOT NULL 约束。However, columns nested within array or map types do not accept NOT NULL constraints.

有关参考信息，请参阅使用和更改表更改列 CREATE TABLE 。For reference information, see CREATE TABLE USING and ALTER TABLE CHANGE COLUMN.

CHECK 约束CHECK constraint

使用 ALTER TABLE ADD CONSTRAINT 和 ALTER TABLE DROP CONSTRAINT 命令管理 CHECK 约束。You manage CHECK constraints using the ALTER TABLE ADD CONSTRAINT and ALTER TABLE DROP CONSTRAINT commands. 在将约束添加到表中之前，ALTER TABLE ADD CONSTRAINT 会验证所有现有行是否满足约束。ALTER TABLE ADD CONSTRAINT verifies that all existing rows satisfy the constraint before adding it to the table.

CREATE TABLE events(
  id LONG NOT NULL,
  date STRING,
  location STRING,
  description STRING
) USING DELTA;

ALTER TABLE events ADD CONSTRAINT dateWithinRange CHECK (date > '1900-01-01');
ALTER TABLE events DROP CONSTRAINT dateWithinRange;

有关参考信息，请参阅 ALTER TABLE ADD constraint 和 ALTER table DROP constraint。For reference information, see ALTER TABLE ADD CONSTRAINT and ALTER TABLE DROP CONSTRAINT.

CHECK 约束是和命令的输出中的表 DESCRIBE DETAIL 属性 SHOW TBLPROPERTIES 。CHECK constraints are table properties in the output of the DESCRIBE DETAIL and SHOW TBLPROPERTIES commands.

ALTER TABLE events ADD CONSTRAINT validIds CHECK (id > 1000 and id < 999999);
DESCRIBE DETAIL events;