Solved

Drop the Auto_increment and primary key

Posted on 2013-12-02
11
398 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

930 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

13 Experts available now in Live!

Get 1:1 Help Now