约束Constraints

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
);

ALTER TABLE events CHANGE COLUMN date DROP NOT NULL;

可以使用 ALTER TABLE CHANGE COLUMN 命令向现有 Delta 表添加 NOT NULL 约束。You can add NOT NULL constraints to an existing Delta table using the ALTER TABLE CHANGE COLUMN command.

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

ALTER TABLE events CHANGE COLUMN id SET NOT NULL;

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

有关详细信息,请参阅创建 Delta 表For details, see Create Delta table.

CHECK 约束CHECK constraint

备注

  • 适用于 Databricks Runtime 7.4 及更高版本。Available in Databricks Runtime 7.4 and above.
  • 在 Databricks Runtime 7.3 LTS 中,你可以写入已定义 CHECK 约束的表,但不能创建 CHECK 约束。In Databricks Runtime 7.3 LTS you can write to tables with CHECK constraints defined but you cannot create CHECK constraints.

使用 ALTER TABLE ADD CONSTRAINTALTER 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
);

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

CHECK 约束在 DESCRIBE DETAILSHOW TBLPROPERTIES 命令的输出中显示为表属性。CHECK constraints appear as 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;

显示的约束Displayed constraint

有关详细信息,请参阅 ADD CONSTRAINTFor details, see ADD CONSTRAINT.