Difference between revisions of "Portable SQL/Statements/IDs/Generating IDs"

From Linuxnetworks
Jump to: navigation, search
(MySQL and SQLite)
 
(→‎MySQL)
Line 1: Line 1:
 
== MySQL ==
 
== MySQL ==
 +
 +
A table can have only one column of an integer type for which an ID is generated. It must be part of a PRIMARY or UNIQUE key and "NOT NULL" should be part of the column definition.
 +
 +
CREATE TABLE ''table'' (
 +
  ''id'' <type> NOT NULL AUTO_INCREMENT PRIMARY KEY
 +
);
  
 
  CREATE TABLE ''table'' (
 
  CREATE TABLE ''table'' (
−
   ''id'' <type> NOT NULL AUTO_INCREMENT
+
   ''id'' <type> NOT NULL AUTO_INCREMENT,
 +
  ''name'' VARCHAR(20),
 +
CONSTRAINT ''unq_table_id_name''
 +
  UNIQUE KEY (id,name)
 
  );
 
  );
  
−
The column types can be:
+
Allowed column types are only integer types like:
 
* TINYINT
 
* TINYINT
 
* SMALLINT
 
* SMALLINT

Revision as of 20:38, 29 August 2007

MySQL

A table can have only one column of an integer type for which an ID is generated. It must be part of a PRIMARY or UNIQUE key and "NOT NULL" should be part of the column definition. 

CREATE TABLE table (
  id <type> NOT NULL AUTO_INCREMENT PRIMARY KEY
);

CREATE TABLE table (
  id <type> NOT NULL AUTO_INCREMENT,
  name VARCHAR(20),
CONSTRAINT unq_table_id_name
  UNIQUE KEY (id,name)
);

Allowed column types are only integer types like:

  • TINYINT
  • SMALLINT
  • INTEGER
  • BIGINT

SQLite

CREATE TABLE table (
  id INTEGER NOT NULL AUTOINCREMENT
);

Only INTEGER column types are allowed for generated IDs.

Retrieved from "https://www.linuxnetworks.de/doc/index.php?title=Portable_SQL/Statements/IDs/Generating_IDs&oldid=2883"