Solved

Drop the Auto_increment and primary key

Posted on 2013-12-02
11
403 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

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.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

813 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

10 Experts available now in Live!

Get 1:1 Help Now