Developing By Me

Project Development and Design

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)

%d bloggers like this: