Hi Anel! I've reviewed your patch and generally things look good. You do some good analysis usually and I think we should highlight your work to the broader developer scene. I've thus CC'ed this email to the maria-developers mailing list. This gives us a chance to get feedback from outside, particularly as this feature is of general interest to the community. Please include the .result files in future versions as it makes reviewing a lot easier. Serg: coding style and implementation aside, I'll do the review on that bit, please have a look at Anel's patch and suggest if you think the current table header for I_S.check_constraints is good enough for our needs or if you have more input on this task. Relevant bit: +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}, + {"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} +}; Vicențiu On Thu, 29 Mar 2018 at 12:01 Anel Husakovic <anel@mariadb.org> wrote:

Hi Vin,

I'm pleased to announce my new patch prototype for MDEV 14474 where are implemented field and table constraints:

https://github.com/an3l/server/commit/f92fd77d2695be52faf280245faa014c44617a...

According to the standard there are only 4 column to represent. CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME,CHECK_CLAUSE But because Serg mentioned that alternative would be to change *information_schema.table_constraints* with adding only 1 column ( *check_clause*) I also used 2 additional columns which are represented also in *table_constraints *and they are TABLE_SHEMA , TABLE_NAME.

Function of interest is: get_check_constraints_record

Also I added 2 test cases:

One that works with only 1 table: MDEV14474-one_table_play.test <https://github.com/an3l/server/commit/f92fd77d2695be52faf280245faa014c44617aa7#diff-2267c689543c97ce71367793d38d4581> Second that works with more tables: MDEV14474-I_S_check_constraint.test <https://github.com/an3l/server/commit/f92fd77d2695be52faf280245faa014c44617aa7#diff-55f166b9ea0852676b6810a441f91cf5>

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`

If the name of constraint = name of the field , than definitely it is not consistent with* show create table* output, and *show create table* output *needs to be changed*, , but this is question that I would like to discuss with you ?!

When you try to use show create table x [image: image.png]

I'm waiting on your reply for global temporary tables as well as check_constraints and how to proceed further

*Not understand yet a concept*:

In *sql/table.h* There is a struct* st_schema_table *which has fill data and process data.

How they work, and how to use that on other database for example PSI.

Regards,

Anel