MySQL Bugs: #5586: Suggestions relating to autonumber data type

Bug #5586	Suggestions relating to autonumber data type
Submitted:	15 Sep 2004 7:39	Modified:	23 Jan 2006 12:11
Reporter:	keith allpress	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:		OS:
Assigned to:		CPU Architecture:	Any

Description:
The following are three suggestions pertaining to the autonumber data type.
 
1.  The restriction exists that there can only be one autonumber column and it must be defined as a primary key.

The suggestion is to relax these restrictions.  I cannot see that they are a logical necessity.

2.  In the case of a composite key, the autoincrement on one of the columns is disallowed, so the suggestion is to allow it.

order=1000 orderline=1
order=1000 orderline=2
order=1000 orderline=3
order=1001 orderline=1
order=1001 orderline=2
etc. 

The application would be releved of the burden of locking the table,.
querying to find the present maximum, incrementing, inserting and releasing the table lock.

3.  Unique random numbers are preferable to autonumbered values whenever meaningless surrogate key values are required, so the suggestion is to define a novel unique_random data type extension.

id  char 10 unique_random
id  int   11 unique_random

The application would be relieved of the burden of a collision test.

K.

How to repeat:
Feature request only

Suggested fix:
Feature request only

I agree that better autonumber are needed, but I have a different suggestion. I think that 
AUTOINCREMENT columns should remain as they are (because they are very fast and for 
backward compatibility) but you should implement SEQUENCEs. SEQUENCEs are standard 
and very flexable, because you can have loops, steps different from 1, etc.

Are SEQUENCEs already on your to-do?

I dont see how additional features necessarily break backward compatibility of existing features.

Thank you for a feature request. Please, read the manual (http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html) about auto_increment. It explains that your suggestion #2 is already implemented for MyISAM and BDB storage engines.

> 1.  The restriction exists that there can only be one autonumber column and
> it must be defined as a primary key.

It should be indexed, not primary key. As for several autonumbering columns, never seen that in any RDBMS.

> 3.  Unique random numbers are preferable to autonumbered values whenever
> meaningless surrogate key values are required, so the suggestion is to
> define a novel unique_random data type extension.

This is also uncommon feature for a database. This feature (random surrogate key) should be implemented in those applications that need it. By that way, randomness and uniqueness are mutual exclusive concepts...

Support for sequences (in Oracle sense) is a different story. I believe, sequences will be supported in MySQL some day. They really allow to solve at least probplems #1 and #2 you had pointed out.