MySQL Bugs: #11640: automatic key creation on CREATE TABLE ... SELECT

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

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

A comment on this ancient feature request.  It would be handy to have a log of temporary indexes created during the execution of queries.

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.