PRIMARY KEY
PRIMARY KEYs are set differently for each database. The above is how SQL Server handles the IDENTITY. However, there are other ways to handle this functionality for primary keys and auto numbering of columns. The following table is the PRIMARY KEY syntax.
| SQL Syntax | Description | SQL Database |
| CREATE TABLE table_name (id INT NOT NULL,PRIMARY KEY (id))
|
Creates a table with a PRIMARY KEY | MySQL, PostgreSQL |
| CREATE TABLE table_name (id INT NOT NULL PRIMARY KEY) | Creates a table with a PRIMARY KEY | SQL Server, Oracle, PostgreSQL |
| CREATE TABLE table_name (id INT NOT NULL,CONSTRAINT index_name PRIMARY KEY (id))
|
Creates a table with a PRIMARY KEY | MySQL, SQL Server, Oracle, PostgreSQL |
The next table is the syntax for IDENTITY’s or auto numbered fields. Each table can only have one IDENTITY field which depending on the database engine can be either numeric only or alpha-numeric in nature.
| SQL Syntax | Description | SQL Database |
| CREATE TABLEtable1 (id INT NOT NULL AUTO_INCREMENT,PRIMARY KEY(id)) | Creates a table with a PRIMARY KEY and an IDENTITY | MySQL |
| CREATE TABLEtable1 (id INT PRIMARY KEY IDENTITY) | Creates a table with a PRIMARY KEY and an IDENTITY | SQL Server |
| CREATE SEQUENCEseq_table1MINVALUE1START WITH1INCREMENT BY 1
CACHE 10
|
Creates the sequence object to be used in a table as an IDENTITY | Oracle (use seq_table1.nextval to get the identity)PostgreSQL (use nextval(‘seq_table1’) to get the identity) |
