Hi, Vicențiu! On Mar 31, Vicențiu Ciorbaru wrote:
+ST_FIELD_INFO check_constraints_fields_info[]= +{ + {"CONSTRAINT_CATALOG", FN_REFLEN, MYSQL_TYPE_STRING, 0, 0, 0, OPEN_FULL_TABLE}, + {"CONSTRAINT_SCHEMA", NAME_CHAR_LEN, MYSQL_TYPE_STRING, 0, 0, 0, + OPEN_FULL_TABLE}, + {"CONSTRAINT_NAME", NAME_CHAR_LEN, MYSQL_TYPE_STRING, 0, 0, 0, + OPEN_FULL_TABLE}, + {"TABLE_SCHEMA", NAME_CHAR_LEN, MYSQL_TYPE_STRING, 0, 0, 0, OPEN_FULL_TABLE},
Why TABLE_SCHEMA? Is there any possibility for it to be different from CONSTRAINT_SCHEMA?
+ {"TABLE_NAME", NAME_CHAR_LEN, MYSQL_TYPE_STRING, 0, 0, 0, OPEN_FULL_TABLE}, + {"CHECK_CLAUSE", NAME_CHAR_LEN, MYSQL_TYPE_STRING, 0, 0, 0, + OPEN_FULL_TABLE}, + {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, SKIP_OPEN_TABLE} +};
Last night I had a problem with field constraints. As a reference I used only way how currently all check constraints are shown and its obtained via* show create table t;*
Bellow is shown that field constraints were not visible as well as that name used for them is *NULL*, at least this is how *show create table* represented them ( there is no way to give a name to a field constraint* for example: f int constraint name check(f<0)*). According to my opinion, because this constraint belongs to the field, it should be also called as a name of a field and not NULL. As an argument to my opinion here is mariadb kb https://mariadb.com/kb/en/library/constraint/ Please take a look at *constraint **name*, which is field constraint and error message.
INSERT INTO t2(name, start_date, end_date) VALUES('Io', '2003-12-15', '2014-11-09');ERROR 4022 (23000): CONSTRAINT `name` failed for `test`.`t2`
Good point. So, practically constraint name is the same as the field name. SQL standard is ok with that, it says "an implementation defined name". But there's still a problem, one can create a table constraint with the same name: MariaDB [test]> create table t1 (a int, b int check (a>b), constraint b check (a<5)); MariaDB [test]> insert t1 values (1,2); ERROR 4025 (23000): CONSTRAINT `b` failed for `test`.`t1` MariaDB [test]> insert t1 values (10,2); ERROR 4025 (23000): CONSTRAINT `b` failed for `test`.`t1` MariaDB [test]> See? Same constraint name in the error message, but it means different constraints! This is a bug. Compare: MariaDB [test]> create table t1 (a int, b int, constraint b check (a>b), constraint b check (a<5)); ERROR 1826 (HY000): Duplicate CHECK constraint name 'b' Regards, Sergei Chief Architect MariaDB and security@mariadb.org