Bug #11640 automatic key creation on CREATE TABLE ... SELECT
Submitted: 29 Jun 2005 13:29 Modified: 3 Oct 2008 20:22
Reporter: Thomas Belot Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: DDL Severity:S4 (Feature request)
Version: OS:Any
Assigned to: CPU Architecture:Any

[29 Jun 2005 13:29] Thomas Belot
Description:
When one does a :
CREATE TABLE test2 
AS 
SELECT * 
FROM users u, connections c
WHERE t1.uid=t2.uid
GROUP BY u.uid, c.conn_id

It would be nice if mysql automatically specifies the primary key using GROUP BY values like :
PRIMARY KEY (uid,conn_id)

In the same idea, when creating a table from a 
CREATE TABLE table_backup AS SELECT * FROM table
It would be nice if mysql placed the same primary key, indexes and unique as in the original table 

I think this would be usefull as my users are *playing* with test tables and don't have a direct access to the server (mysqldump)

How to repeat:
not a bug
[11 Jul 2008 9:46] Andrew McGill
A comment on this ancient feature request.  It would be handy to have a log of temporary indexes created during the execution of queries.
[3 Oct 2008 20:22] Konstantin Osipov
You can specify the primary key you like in CREATE TABLE .. SELECT syntax:

mysql> create table t1 (a int primary key) select 1 as a;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `a` int(11) NOT NULL,
  PRIMARY KEY (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

If you would still like MySQL to auto-detect and create the potential key, 
it's of course a valid request, but I'm not aware of any plans to implement it.

Thank you for your interest in MySQL.