-
Praveenkumar Hulakund authored
The aim of this WL is to implement CHECK constraints feature. The feature is supported for all the storage engines. The WL provides following functionality: a) Create check constraints on a table: WL implements following syntax to add check constraint in CREATE TABLE and ALTER TABLE, check_constraint_definition: [CONSTRAINT [symbol]] CHECK(expr) [[NOT] ENFORCED] Example: CREATE TABLE t1 (f1 INT CHECK (f1 < 929), f2 INT, CHECK (f2 < 929)); ALTER TABLE t1 ADD COLUMN f3 INT CHECK (f3 < 929), ADD CONSTRAINT ck CHECK (f2 > 1); If enforcement is not specified then check constraint is created in the ENFORCED state. b) Check constraints validation: For DML operations INSERT, UPDATE, REPLACE, LOAD DATA, LOAD XML, check constraints in ENFORCED states are validated on each row. The check constraint is satisfied if and only if expression is evaluated to TRUE or UNKNOWN(for NULL column value). Constraint violation error is reported otherwise. Example: CREATE TABLE t (s1 INT, CHECK (s1 > 0)) INSERT INTO t VALUES (-1) /* this should fail, the condition is FALSE */ INSERT INTO t VALUES (NULL) /* this should succeed, the condition is UNKNOWN */ INSERT INTO t VALUES (1) /* this should succeed, the condition is TRUE */ c) Alter check constraint's state: WL implements following syntax in ALTER TABLE statement to alter constraint's state. ALTER TABLE ... ALTER CHECK symbol [[NOT] ENFORCED] Example: ALTER TABLE t1 ALTER CHECK ck NOT ENFORCED; ALTER TABLE t1 ALTER CHECK ck ENFORCED; d) Drop check constraint: WL implements following syntax in ALTER TABLE statement to DROP check constraint. ALTER TABLE ... DROP CHECK symbol; Example: ALTER TABLE t1 DROP CHECK ck; e) SHOW CREATE TABLE lists check constraints defined on the table. Example: CREATE TABLE t1( f1 INT CHECK (f1 < 10), f2 INT CHECK (f2 < 10) NOT ENFORCED); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `f1` int(11) DEFAULT NULL, `f2` int(11) DEFAULT NULL, CONSTRAINT `t1_chk_1` CHECK ((`f1` < 10)), CONSTRAINT `t1_chk_2` CHECK ((`f2` < 10)) /*!80015 NOT ENFORCED */, ); All check constraints are listed in the table check constraint form. Check constraint in not enforced state are listed with the conditional comment "NOT ENFORCED". f) INFORMATION_SCHEMA.CHECK_CONSTRAINTS is introduced to list check constraints. g) INFORMATION_SCHEMA.TABLE_CONSTRAINTS now lists even check constraints defined on the table. Column "ENFORCED" is introduced to TABLE_CONSTRAINTS table to list enforcement state [YES/NO] for constraints. h) WL implements core features defined in the ANSI/ISO SQL standard E141-06 "CHECK CONSTRAINTS". Please refer WL's HLS section R for the common restrictions on the check constraints. Change-Id: I9de7158200e829e05df322f8ffe7e782aa9b7ab1
Praveenkumar Hulakund authoredThe aim of this WL is to implement CHECK constraints feature. The feature is supported for all the storage engines. The WL provides following functionality: a) Create check constraints on a table: WL implements following syntax to add check constraint in CREATE TABLE and ALTER TABLE, check_constraint_definition: [CONSTRAINT [symbol]] CHECK(expr) [[NOT] ENFORCED] Example: CREATE TABLE t1 (f1 INT CHECK (f1 < 929), f2 INT, CHECK (f2 < 929)); ALTER TABLE t1 ADD COLUMN f3 INT CHECK (f3 < 929), ADD CONSTRAINT ck CHECK (f2 > 1); If enforcement is not specified then check constraint is created in the ENFORCED state. b) Check constraints validation: For DML operations INSERT, UPDATE, REPLACE, LOAD DATA, LOAD XML, check constraints in ENFORCED states are validated on each row. The check constraint is satisfied if and only if expression is evaluated to TRUE or UNKNOWN(for NULL column value). Constraint violation error is reported otherwise. Example: CREATE TABLE t (s1 INT, CHECK (s1 > 0)) INSERT INTO t VALUES (-1) /* this should fail, the condition is FALSE */ INSERT INTO t VALUES (NULL) /* this should succeed, the condition is UNKNOWN */ INSERT INTO t VALUES (1) /* this should succeed, the condition is TRUE */ c) Alter check constraint's state: WL implements following syntax in ALTER TABLE statement to alter constraint's state. ALTER TABLE ... ALTER CHECK symbol [[NOT] ENFORCED] Example: ALTER TABLE t1 ALTER CHECK ck NOT ENFORCED; ALTER TABLE t1 ALTER CHECK ck ENFORCED; d) Drop check constraint: WL implements following syntax in ALTER TABLE statement to DROP check constraint. ALTER TABLE ... DROP CHECK symbol; Example: ALTER TABLE t1 DROP CHECK ck; e) SHOW CREATE TABLE lists check constraints defined on the table. Example: CREATE TABLE t1( f1 INT CHECK (f1 < 10), f2 INT CHECK (f2 < 10) NOT ENFORCED); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `f1` int(11) DEFAULT NULL, `f2` int(11) DEFAULT NULL, CONSTRAINT `t1_chk_1` CHECK ((`f1` < 10)), CONSTRAINT `t1_chk_2` CHECK ((`f2` < 10)) /*!80015 NOT ENFORCED */, ); All check constraints are listed in the table check constraint form. Check constraint in not enforced state are listed with the conditional comment "NOT ENFORCED". f) INFORMATION_SCHEMA.CHECK_CONSTRAINTS is introduced to list check constraints. g) INFORMATION_SCHEMA.TABLE_CONSTRAINTS now lists even check constraints defined on the table. Column "ENFORCED" is introduced to TABLE_CONSTRAINTS table to list enforcement state [YES/NO] for constraints. h) WL implements core features defined in the ANSI/ISO SQL standard E141-06 "CHECK CONSTRAINTS". Please refer WL's HLS section R for the common restrictions on the check constraints. Change-Id: I9de7158200e829e05df322f8ffe7e782aa9b7ab1
Loading