AlterTableAddConstraintDeltaCommand¶

AlterTableAddConstraintDeltaCommand is a transactional AlterDeltaTableCommand to register a new CHECK constraint (when altering a delta table with AddConstraint table changes).

AlterTableAddConstraintDeltaCommand represents the ALTER TABLE ADD CONSTRAINT SQL command.

Creating Instance¶

AlterTableAddConstraintDeltaCommand takes the following to be created:

• DeltaTableV2
• Constraint Name
• Constraint SQL Expression (text)

AlterTableAddConstraintDeltaCommand is created when:

• DeltaCatalog is requested to alter a delta table (with AddConstraint table changes)

Executing Command¶

run(
  sparkSession: SparkSession): Seq[Row]

run is part of the RunnableCommand (Spark SQL) abstraction.

Begin Transaction¶

run starts a transaction.

Update Metadata¶

run creates a new Metadata with the configuration altered to include the name and the given exprText.

Check Existing Data (Full Scan)¶

run prints out the following INFO message to the logs:

Checking that [exprText] is satisfied for existing data.
This will require a full table scan.

run creates a DataFrame that represents the version of the delta table (based on the Snapshot and the AddFiles). run counts the records that satisfy the WHERE clause:

((NOT [expr]) OR ([expr] IS UNKNOWN))

Commit Transaction¶

With no rows violating the check constraint, run commits the transaction with the new Metadata and ADD CONSTRAINT operation (with the given name and exprText).

In the end, run returns an empty collection.

AnalysisExceptions¶

Illegal Constraint Name¶

run throws an AnalysisException when the name of the table constraint is __CHAR_VARCHAR_STRING_LENGTH_CHECK__:

Cannot use '__CHAR_VARCHAR_STRING_LENGTH_CHECK__' as the name of a CHECK constraint.

Constraint Already Exists¶

run throws an AnalysisException when the given name is already in use (by an existing constraint):

Constraint '[name]' already exists as a CHECK constraint. Please delete the old constraint first.
Old constraint:
[oldExpr]

Constraint Non-Boolean¶

run throws an AnalysisException when the given exprText does not produce a BooleanType value:

CHECK constraint '[name]' ([expr]) should be a boolean expression.

Constraint Violation¶

run throws an AnalysisException when there are rows that violate the new constraint (with the name of the delta table and the exprText):

[num] rows in [name] violate the new CHECK constraint ([exprText])

RunnableCommand¶

AlterTableAddConstraintDeltaCommand is a LeafRunnableCommand (Spark SQL) logical operator.

IgnoreCachedData¶

AlterTableAddConstraintDeltaCommand is a IgnoreCachedData (Spark SQL) logical operator.

Demo¶

Create Table¶

sql("""
DROP TABLE IF EXISTS delta_demo;
""")

sql("""
CREATE TABLE delta_demo (id INT)
USING delta;
""")

Adding Constraint¶

Enable logging to see the following INFO message in the logs:

Checking that id > 5 is satisfied for existing data.
This will require a full table scan.

sql("""
ALTER TABLE delta_demo
ADD CONSTRAINT demo_check_constraint CHECK (id > 5);
""")

DESC HISTORY¶

sql("""
DESC HISTORY delta_demo;
""")
  .select('version, 'operation, 'operationParameters)
  .show(truncate = false)

+-------+--------------+-----------------------------------------------------------------------------+
|version|operation     |operationParameters                                                          |
+-------+--------------+-----------------------------------------------------------------------------+
|1      |ADD CONSTRAINT|{name -> demo_check_constraint, expr -> id > 5}                              |
|0      |CREATE TABLE  |{isManaged -> true, description -> null, partitionBy -> [], properties -> {}}|
+-------+--------------+-----------------------------------------------------------------------------+

DeltaInvariantViolationException¶

sql("""
INSERT INTO delta_demo VALUES 3;
""")

DeltaInvariantViolationException: CHECK constraint demo_check_constraint (id > 5) violated by row with values:
 - id : 3

Metadata¶

CHECK constraints are stored in Metadata of a delta table.

sql("""
SHOW TBLPROPERTIES delta_demo;
""")
  .where('key startsWith "delta.constraints.")
  .show(truncate = false)

+---------------------------------------+------+
|key                                    |value |
+---------------------------------------+------+
|delta.constraints.demo_check_constraint|id > 5|
+---------------------------------------+------+

Logging¶

Enable ALL logging level for org.apache.spark.sql.delta.commands.AlterTableAddConstraintDeltaCommand logger to see what happens inside.

Add the following line to conf/log4j.properties:

log4j.logger.org.apache.spark.sql.delta.commands.AlterTableAddConstraintDeltaCommand=ALL

Refer to Logging.