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 MySQLinformation_schemacontains a number of tables. Among them, one is calledTABLE_CONSTRAINTS. Unsurprisingly, each row inTABLE_CONSTRAINTStable represents a single table constraint. TheTABLE_CONSTRAINTStable 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_CONSTRAINTStable that is sufficient to reliably identify a single row in theinformation_schema.TABLE_CONSTRAINTStable.- 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)
4 comments:
I think the right answer is the combination of:
CONSTRAINT_NAME
TABLE_SCHEMA
TABLE_NAME
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 :)
Regards
Hans Stevens
I'd say that 2 columns are enough.
*CONSTRAINT_NAME
*TABLE_NAME
but there are cases where you will need all columns but
*CONSTRAINT_CATALOG
and
*CONSTRAINT_SCHEMA
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
columnlist_constraintype
you eliminate the need for
*CONSTRAINT_TYPE
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:
CONSTRAINT_SCHEMA
CONSTRAINT_NAME
TABLE_NAME`
CONSTRAINT_TYPE
- Diego
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?
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.
Post a Comment