Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 418
  • Last Modified:

Drop the Auto_increment and primary key

dear all,

right now I have a execise to find out, table by table, the field that is type

NOT NULL AUTO_INCREMENT

Open in new window


and

PRIMARY KEY (`xxx`)

Open in new window


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" ;

Open in new window


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>';

Open in new window


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 ?
0
marrowyung
Asked:
marrowyung
  • 8
  • 3
1 Solution
 
NopiusCommented:
Auto increment field is defined per-table, it's value also can be accessed by:

describe information_schema.tables;

Open in new window


> but how to find out which one tells which column is "AUTO_INCREMENT" one?

easy:
show create table '<table>';

Open in new window


you will see which field is defined as auto incremented or
describe table '<table>';

Open in new window


> is that the result of the AUTO_INCREMENT column has the value of 7, what is that mean?

This means that next value of inserted column with auto-incremented field will be assigned to '7', after that this value (in information_schema) will be also incremented.

> what is the way to drop both the primary key and the auto increment option ?

Really this depends on engine. In InnoDB dropping primary index is not allowed in this case, trying to drop it results an error:
mysql> alter table test_auto drop primary key;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

Open in new window


What you can do here:
1) Remove auto-increment from primary key:
mysql> alter table test_auto modify column id int not null;
Query OK, 1 row affected (0.30 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show create table test_auto
    -> ;
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                      |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| test_auto | CREATE TABLE `test_auto` (
  `id` int(11) NOT NULL,
  `text` varchar(2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------+

Open in new window


2) Remove primary key:
mysql> alter table test_auto drop primary key ;

Open in new window

0
 
marrowyungAuthor Commented:
"Auto increment field is defined per-table, it's value also can be accessed by:"

but that one only show field, types but don't know how to use it:

(Field, Type, Null, Key, Default, Extra) VALUES
('TABLE_CATALOG', 'varchar(512)', 'NO', '', '', ''), 
('TABLE_SCHEMA', 'varchar(64)', 'NO', '', '', ''), 
('TABLE_NAME', 'varchar(64)', 'NO', '', '', ''), 
('TABLE_TYPE', 'varchar(64)', 'NO', '', '', ''), 
('ENGINE', 'varchar(64)', 'YES', '', NULL, ''), 
('VERSION', 'bigint(21) unsigned', 'YES', '', NULL, ''), 
('ROW_FORMAT', 'varchar(10)', 'YES', '', NULL, ''), 
('TABLE_ROWS', 'bigint(21) unsigned', 'YES', '', NULL, ''), 
('AVG_ROW_LENGTH', 'bigint(21) unsigned', 'YES', '', NULL, ''), 
('DATA_LENGTH', 'bigint(21) unsigned', 'YES', '', NULL, ''), 
('MAX_DATA_LENGTH', 'bigint(21) unsigned', 'YES', '', NULL, ''), 
('INDEX_LENGTH', 'bigint(21) unsigned', 'YES', '', NULL, ''), 
('DATA_FREE', 'bigint(21) unsigned', 'YES', '', NULL, ''), 
('AUTO_INCREMENT', 'bigint(21) unsigned', 'YES', '', NULL, ''), 
('CREATE_TIME', 'datetime', 'YES', '', NULL, ''), 
('UPDATE_TIME', 'datetime', 'YES', '', NULL, ''), 
('CHECK_TIME', 'datetime', 'YES', '', NULL, ''), 
('TABLE_COLLATION', 'varchar(32)', 'YES', '', NULL, ''), 
('CHECKSUM', 'bigint(21) unsigned', 'YES', '', NULL, ''), 
('CREATE_OPTIONS', 'varchar(255)', 'YES', '', NULL, ''), 
('TABLE_COMMENT', 'varchar(2048)', 'NO', '', '', '')

Open in new window


I don't see it provide much help, or what value you want me to take a look ?


"show create table '<table>'; "

this one, gives error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near "<table>" at line 1

Open in new window


"describe table '<table>'; "

this one also gives error:

 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table  <table>at line 1

Open in new window


"
This means that next value of inserted column with auto-incremented field will be assigned to '7', after that this value (in information_schema) will be also incremented.
"

so it means the CURRENT value is 7 ?

"What you can do here:
1) Remove auto-increment from primary key:"

What you mean is if we are using InnoDB, we can only remove auto increment first then drop primary key but not drop the primary key first ?
0
 
marrowyungAuthor Commented:
so dropping FOREIGN KEY  is

alter table test_auto drop foreigh key ; 

Open in new window

?

how about dropping constraint ?

if  I see table structure like this:

CREATE TABLE `YYYY` (
  `SCHED_NAME` varchar(120) NOT NULL,
  `TRIGGER_NAME` varchar(200) NOT NULL,
  `TRIGGER_GROUP` varchar(200) NOT NULL,
  `REPEAT_COUNT` bigint(7) NOT NULL,
  `REPEAT_INTERVAL` bigint(12) NOT NULL,
  `TIMES_TRIGGERED` bigint(10) NOT NULL,
  PRIMARY KEY (`SCHED_NAME`,`TRIGGER_NAME`,`TRIGGER_GROUP`),
  KEY `SCHED_NAME` (`SCHED_NAME`,`TRIGGER_NAME`,`TRIGGER_GROUP`),
  CONSTRAINT `yyyy_ibfk_1` FOREIGN KEY (`SCHED_NAME`, `TRIGGER_NAME`, `TRIGGER_GROUP`) REFERENCES `XXXX` (`SCHED_NAME`, `TRIGGER_NAME`, `TRIGGER_GROUP`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Open in new window


If I don't drop constaint and/or primary key, it seemt the truncate of table of yyyy is not possible as I have to also truncate table of xxxx at the same time ?
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
marrowyungAuthor Commented:
so the exact field with auto increment can't be found out ?
0
 
NopiusCommented:
It can.

mysql> describe test_auto;
mysql> describe test_auto;
+-------+------------+------+-----+---------+----------------+
| Field | Type       | Null | Key | Default | Extra          |
+-------+------------+------+-----+---------+----------------+
| text  | varchar(2) | YES  |     | NULL    |                |
| id    | int(11)    | NO   | PRI | NULL    | auto_increment |
+-------+------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

Open in new window


You see 'PRI' near primary key and 'auto_increment' near auto incremented.

Or alternatively:
mysql> show create table test_auto;
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                      |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_auto | CREATE TABLE `test_auto` (
  `text` varchar(2) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Open in new window

0
 
NopiusCommented:
> but that one only show field, types but don't know how to use

You should select from information schema:

mysql> select * from information_schema.tables where auto_increment is not null;
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME         | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION   | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT |
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------------+
| def           | test         | test_auto  | BASE TABLE | InnoDB |      10 | Compact    |          1 |          16384 |       16384 |               0 |            0 |         0 |              2 | 2013-12-03 15:56:59 | NULL        | NULL       | latin1_swedish_ci |     NULL |                |               |
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------------+
1 row in set (0.00 sec)

Open in new window


You will see all tables where auto_increment field present.

> "show create table '<table>'; "
> "describe table '<table>'; "

You should replace `<table>` with your table name, in my case 'test_auto' (for all the rows selected with auto increment fields above):

mysql> show create table test.test_auto;
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                      |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_auto | CREATE TABLE `test_auto` (
  `text` varchar(2) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Open in new window


> so it means the CURRENT value is 7 ?

This means that the NEXT inserted value will be 7, not current.


> so dropping FOREIGN KEY  is ...
0
 
marrowyungAuthor Commented:
So the format is not:

describe table '<table>'; 

Open in new window


it is just describe <table>, thanks for that.

but how can we make use of that extra * field ? the * always there?

so we set @var1 = describe <table> ? but we can't use where to filter out that column ?

"show create table test_auto;"

but this one just script out the create table script, right? but we can't easliy return the column name that has auto increment type, right? or how ? In this case seems describe is better.

this one :

  select * from information_schema.tables where auto_increment is not null;

only should which table contain this but don't show which FIELD/column defined by that .

I need to find that out in order to do this :

alter table test_auto modify column id int not null;

Open in new window


and then I wil drop primary key after that.
0
 
marrowyungAuthor Commented:
if we have a column with field like this :

  `DISPLAY_RESOURCE_ID` bigint(19) NOT NULL AUTO_INCREMENT COMMENT 'PRIMARY KEY',

Open in new window


doing this:

alter table test_auto modify column DISPLAY_RESOURCE_ID bigint(19) not null;

will do any -ve effect?
0
 
marrowyungAuthor Commented:
0
 
marrowyungAuthor Commented:
finally, I find it out myself that only this simple one will help, the EXTRA field record this:

    SELECT COLUMN_NAME,COLUMN_TYPE,  Column_default, IS_NULLABLE , COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = 'database name ' AND TABLE_NAME = 'tablename' and COLUMN_KEY= "PRI" and EXTRA='auto_increment';

Open in new window


this one wills show you exaclty which field is part of the primary key and  also the auto_increment!!

then I will use concat to build a statement using all returned valuable to drop the auto increment.
0
 
marrowyungAuthor Commented:
use this one instead INFORMATION_SCHEMA.COLUMNS .
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 8
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now