Cannot find table details in mysql information_schema

Question

Hi,

I am working on an assignment where I am supposed to find all of the tables in information_schema of a "blank" database. I can do this in either MySQL or PostgreSQL. I chose MySQL. So, I have identified all of the tables:

1. CHARACTER_SETS
2. COLLATIONS
3. COLLATION_CHARACTER_SET_APPLICABILITY
4. COLUMNS
5. COLUMN_PRIVILEGES
6. INDEX_STATISTICS
7. KEY_COLUMN_USAGE
8. PROFILING
9. ROUTINES
10. SCHEMATA
11. SCHEMA_PRIVILEGES
12. STATISTICS
13. TABLES
14. TABLE_CONSTRAINTS
15. TABLE_PRIVILEGES
16. TABLE_STATISTICS
17. TRIGGERS
18. USER_PRIVILEGES
19. VIEWS

Now, I have to find details about those tables. For example, primary key definitions, table columns, foreign keys, trigger actions, etc.

The problem that I am running into, however, is that none of these tables list any primary keys or foreign keys or constraints, etc.

Am I misinterpreting? Or can this information be found anywhere? I'd appreciate any help.

Thanks!

Answer 1

Have you tried the command DESCRIBE table;?

If there is a primary key you will see...

+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(128) | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+