Thursday, March 27, 2008

"Me Too" MySQL Information Schema popquiz

A few days ago, I wrote how I will be your guide to the Grand Tour of the Information Schema and its Applications which is one of the two talks I will be doing at the upcoming MySQL User's Conference.

In view of the popularity of "Pop Quiz" format so successfully used by Carsten, I feel compelled to imitation, and as a primer to my talk, I'd like to offer you my "Me Too" MySQL Information Schema popquiz. So, here goes...

The MySQL information_schema contains a number of tables. Among them, one is called TABLE_CONSTRAINTS. Unsurprisingly, each row in TABLE_CONSTRAINTS table represents a single table constraint. The TABLE_CONSTRAINTS table has the following columns:

| Field | Type | Null | Key | Default | Extra |
| CONSTRAINT_CATALOG | varchar(512) | YES | | NULL | |
| CONSTRAINT_SCHEMA | varchar(64) | NO | | | |
| CONSTRAINT_NAME | varchar(64) | NO | | | |
| TABLE_SCHEMA | varchar(64) | NO | | | |
| TABLE_NAME | varchar(64) | NO | | | |
| CONSTRAINT_TYPE | varchar(64) | NO | | | |

  • Specify a minimal set of columns of the information_schema.TABLE_CONSTRAINTS table that is sufficient to reliably identify a single row in the information_schema.TABLE_CONSTRAINTS table.

  • Argue why these columns are necessary and sufficient to identify a row, and why a smaller set of columns does not exist

Feel free to post the answer as a comment. Every correct answer will earn a 20% discount to the admission fee. You can get bonus points for nice elegant argumentation, and also if you specify more than one minimal set of columns.

I will elect three to five of the best answers and make sure that whoever posts it gets a laminated printout of the diagram of the MySQL information schema.

(Please make sure you post me your email address if you think you are eligible to either the discount code, the laminated diagram or both)


Hans Stevens said...

I think the right answer is the combination of:


My explanation:

We can remove CONSTRAINT_CATALOG because it is always NULL, since MySQL does not support the concept of a database catalog.

In MySQL, indexes as well as constraints reside in same schema as the table on which they are defined. So CONSTRAINT_SCHEMA is always equal to TABLE_SCHEMA. One of them can be removed.

CONSTRAINT_NAME must always be unique for a schema (So the combination of CONSTRAINT_NAME and TABLE_SCHEMA should be enough you may think), except for primary which always has the name PRIMARY. Therefore TABLE_NAME must be included.

Very nice question!
Hope the response is correct :)

Hans Stevens

Anonymous said...

I'd say that 2 columns are enough.


but there are cases where you will need all columns but

Here is how:
(only two columns argument)
if you name your tables as
database_tablename, this eliminates the need for the CONSTRAINT_SCHEMA and TABLE_SCHEMA (which hold the same data (I think))

So far we are using the TABLE_NAME column

Then, if you name your keys like

you eliminate the need for

And CONSTRAINT_CATALOG can be null (and would actually like to find out what kind of data is supposed to hold).

The only reason why you need the TABLE_NAME column is for PRIMARY KEYs as you cannot change their names.

Now, to make it generic (in cases where you cannot select how tables and columns are named)
you would need these columns as a minimum to identify a row:


- Diego

Anonymous said...

CONSTRAINT_NAME, TABLE_SCHEMA and TABLE_NAME are what you need. Constraints are (currently) defined per-table, so CONSTRAINT_SCHEMA will be the same as TABLE_SCHEMA. CONSTRAINT_CATALOG is always NULL (certainly on the systems I have access to; no idea what it does). CONSTRAINT_TYPE is irrelevant for identifying the row, since _NAME will force a differentiation between those anyway.

Thanks for the kudos btw :-) -- may I presume it's OK to include your quizzes as entries in my collection?

rpbouman said...

Hi guys,

thanks for taking the time to respond, I appreciate it a lot ;)

Shortly, a new entry will appear on the blog providing the full solution.

I can already say that fmpwizard (Diego) provided one correct solution, and in between the lines he provides all the information to derive the second solution as well.

Hans and Carsten come up with the same solution which is close but not correct.


Nowadays, many SQL implementations offer some form of aggregate string concatenation function. Being an aggregate function, it has the effe...