Solved

Drop the Auto_increment and primary key

Posted on 2013-12-02
11
407 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
join tables 4 57
Slow MySQL (InnoDB) Query with Inner Join & GroupBy 7 64
Formating field inside mysql query 2 29
EditableGrid how to fetch rows from MySql in php 14 44
Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
Creating and Managing Databases with phpMyAdmin in cPanel.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

679 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