SQL CREATE ALTER DROP TABLE

Internal

• SQL
• SQL Data Types

CREATE

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] person 
( id smallint, 
  name varchar(30) NOT NULL, 
  CONSTRAINT person_pkey PRIMARY KEY (id)
);

PostgreSQL

MySQL

If the TEMPORARY keyword is used, the created table will be temporary instead of permanent.

ALTER

ALTER TABLE person [...]

Add/Remove a Column

ALTER TABLE person ADD COLUMN birthday DATE;

ALTER TABLE person DROP COLUMN birthday;

PostgreSQL

MySQL

Add/Remove a Constraint

NOT NULL

ALTER TABLE sometable ALTER COLUMN somecolumn SET NOT NULL;

ALTER TABLE sometable ALTER COLUMN somecolumn DROP NOT NULL;

PostgreSQL

MySQL

Primary Key

ALTER TABLE sometable ADD [CONSTRAINT sometable_pkey] PRIMARY KEY (somecolumn);

If the name of the primary key constraint is not provided with the optional CONSTRAINT ..., the command will generate the name of the primary key constraint to match the <table_name>_pkey pattern.

ALTER TABLE sometable DROP CONSTRAINT [IF EXISTS] constraint_name [RESTRICT|CASCADE];

PostgreSQL

MySQL

Foreign Key

ALTER TABLE sometable ADD [CONSTRAINT sometable_someothertable_id_fkey] FOREIGN KEY (someothertable_id) REFERENCES someothertable(id);

If the name of the constraint is not provided with CONSTRAINT ..., the command will generate the name of the foreign key constraint to match the <this_table_name>_<the_other_table_name>_<column_name>_fkey pattern.

ALTER TABLE sometable DROP CONSTRAINT [IF EXISTS] constraint_name [RESTRICT|CASCADE];

PostgreSQL

MySQL

DROP

DROP TABLE person [...]

PostgreSQL

MySQL

Features

AUTO_INCREMENT

AUTO_INCREMENT