maria-developers team mailing list archive

[Sergei Golubchik <serg@xxxxxxxxxxx>]

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@xxxxxxxxxxx