right now I have a execise to find out, table by table, the field that is type
NOT NULL AUTO_INCREMENT
PRIMARY KEY (`xxx`)
The way to find out which column is the primary key should be:
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = '<databasen name>' AND TABLE_NAME = '<table name>' and COLUMN_KEY= "PRI" ;
please correct me if I am wrong.
but how to find out which one tells which column is "AUTO_INCREMENT" one?
or the column which is the primary key will AUTOMATICALLY be the auto_incremented field ?
in a test of:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = '<database>' AND TABLE_NAME = '<table>';
is that the result of the AUTO_INCREMENT column has the value of 7, what is that mean?
what is the way to drop both the primary key and the auto increment option ?