9.5 Summary

Sequences are the most portable way to generate numbers. However, it will become a problem on MySQL, because it does not support the SEQUENCE keyword. For most of the time, the proprietary AUTO_INCREMENT suffices. If you need something more sequence-like, you can emulate them by using a separate table which maintains the database's sequences (the implementation is based on the MySQL-specific LAST_INSERT_ID() function). See the references of this section.

Using DEFAULT and IDENTITY (or AUTO_INCREMENT on MySQL) at the same time should generate an error on every DBMS. I tried to the combination of DEFAULT and AUTO_INCREMENT on MySQL 5.1 and it correctly failed with an error. It would not make much sense anyway. The auto-increment already sets a default, so this collision foreseeable. I have not tested more cases, but the same should apply for all defaulting constructs.

Finally, do not let the auto-increment functionality influence your thinking: it is too tempting to declare every table's primary key as a simple incrementing ID. Try to find the right mix between natural and artificial keys. You can read more about keys in the 13.1 Natural vs. Artificial Keys section.

References for Default Values:

Last updated: 2010-08-04