Updated on 2025-01-09 GMT+08:00

ADD CONSTRAINT

Function

This command adds a CHECK constraint. Before such a constraint is added to a table, the system verifies whether all existing rows meet the constraint.

Precautions

Before adding a constraint to a table, the system verifies whether all existing rows meet the constraint. If any row fails to meet the constraint, the system will not add the constraint. Therefore, you need to delete the rows that do not meet the constraint before adding it.

Syntax

ALTER TABLE [database_name.]table_name|DELTA.`obs://bucket_name/tbl_path`

ADD CONSTRAINT constraint_name

CHECK(boolExpression)

Parameter Description

Table 1 Parameter descriptions of ADD CONSTRAINT

Parameter

Description

database_name

Name of the database, consisting of letters, numbers, and underscores (_)

table_name

Name of the table in the database, consisting of letters, numbers, and underscores (_)

bucket_name

OBS bucket name

tbl_path

Storage location of the Delta table in the OBS bucket

constraint_name

Constraint name

boolExpression

Constraint expression

Required Permissions

  • SQL permissions
Table 2 Permissions required for executing ADD CONSTRAINT

Permission Description

ALTER permission on a table

  • Fine-grained permission: dli:table:alter
  • Metadata services provided by LakeFormation. Refer to the LakeFormation documentation for details on permission configuration.

Example

alter table delta_table0 add constraint const_price check(price>0);
alter table delta.`obs://bucket1/dbgms/h0` add constraint const_id check(id>0);

System Response

Displays whether the task is successfully executed in the execution history or job list.