Solved

Drop the Auto_increment and primary key

Posted on 2013-12-02
11
394 Views
Last Modified: 2013-12-04
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
Comment
Question by:marrowyung
  • 8
  • 3
11 Comments
 
LVL 27

Expert Comment

by:Nopius
ID: 39691465
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39691607
"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
 
LVL 1

Author Comment

by:marrowyung
ID: 39691637
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39691669
so the exact field with auto increment can't be found out ?
0
 
LVL 27

Expert Comment

by:Nopius
ID: 39691689
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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 27

Accepted Solution

by:
Nopius earned 500 total points
ID: 39691727
> 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
 
LVL 1

Author Comment

by:marrowyung
ID: 39691829
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39691849
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39692342
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39697348
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
 
LVL 1

Author Closing Comment

by:marrowyung
ID: 39697350
use this one instead INFORMATION_SCHEMA.COLUMNS .
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now