Documentation

Renaming a Table

Microsoft SQL

EXEC sp_rename products, items

MySQL, PostgreSQL

ALTER TABLE products RENAME TO items

Adding a Column

Microsoft SQL, MySQL, PostgreSQL

ALTER TABLE products ADD description text

Removing a Column

Microsoft SQL, MySQL, PostgreSQL

ALTER TABLE products DROP COLUMN description

Renaming a Column

Microsoft SQL

EXEC sp_rename @objname = 'products.product_no', @newname = 'product_number', @objtype = 'COLUMN'

MySQL

ALTER TABLE product CHANGE product_no product_number INT NOT NULL DEFAULT 0; // omit PRIMARY KEY and UNIQUE

PostgreSQL

ALTER TABLE products RENAME COLUMN product_no TO product_number

Changing a Column's Data Type

Microsoft SQL

ALTER TABLE products ALTER COLUMN price DECIMAL(10, 2)

MySQL

ALTER TABLE products MODIFY COLUMN price DECIMAL(10, 2)

PostgreSQL

ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2)

Reading a Column's Default Value

Microsoft SQL

SELECT * FROM sys.default_constraints

can be used for removing

Changing a Column's Default Value

Microsoft SQL

ALTER TABLE products ADD CONSTRAINT some_name DEFAULT 7.77 FOR price

MySQL, PostgreSQL

ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77

Removing a Column's Default Value

Microsoft SQL

ALTER TABLE products DROP CONSTRAINT default_name

MySQL, PostgreSQL

ALTER TABLE products ALTER COLUMN price DROP DEFAULT

Reading Constraints

Microsoft SQL Server, PostgreSQL

select distinct
	Con.Name, Con.TableName,Con.Type, Col.ColumnName, Col.Position, Con.RefConstraintName, Con.UpdateRule, Con.DeleteRule
from
	(select c.CONSTRAINT_NAME as Name, c.TABLE_NAME as TableName, c.CONSTRAINT_TYPE as Type, r.UNIQUE_CONSTRAINT_NAME as RefConstraintName, r.UPDATE_RULE AS UpdateRule, R.DELETE_RULE as DeleteRule from INFORMATION_SCHEMA.TABLE_CONSTRAINTS c left join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS r ON r.CONSTRAINT_NAME=c.CONSTRAINT_NAME where c.table_catalog='$=db.Name$') as Con,
	(select CONSTRAINT_NAME as ConstraintName, TABLE_NAME as TableName, COLUMN_NAME as ColumnName, ORDINAL_POSITION as Position from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where table_catalog='$=db.Name$') as Col
where
	Con.Name = Col.ConstraintName

MySQL

select distinct
	Con.Name, Con.Type, Con.TableName, Col.ColumnName, Col.Position, Con.RefTableName, Con.RefConstraintName, Con.UpdateRule, Con.DeleteRule
from
	(select c.CONSTRAINT_NAME as Name, c.TABLE_NAME as TableName, c.CONSTRAINT_TYPE as Type, r.REFERENCED_TABLE_NAME as RefTableName, r.UNIQUE_CONSTRAINT_NAME as RefConstraintName, r.UPDATE_RULE AS UpdateRule, R.DELETE_RULE as DeleteRule from INFORMATION_SCHEMA.TABLE_CONSTRAINTS c left join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS r ON r.CONSTRAINT_NAME=c.CONSTRAINT_NAME where c.table_schema='$=db.Name$') as Con,
	(select CONSTRAINT_NAME as ConstraintName, TABLE_NAME as TableName, COLUMN_NAME as ColumnName, ORDINAL_POSITION as Position from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where table_schema='$=db.Name$') as Col
where
	Con.Name = Col.ConstraintName

Removing a Constraint

Microsoft SQL, PostgreSQL

ALTER TABLE products DROP CONSTRAINT some_name

MySQL

Not Available

Adding a Check Constraint

Microsoft SQL

ALTER TABLE products ADD CONSTRAINT some_name CHECK (name <> '') // before add: WITH NOCHECK

MySQL

Not Available

PostgreSQL

ALTER TABLE products ADD CONSTRAINT some_name CHECK (name <> '')

Adding a Unique Constraint

Microsoft SQL, PostgreSQL

ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no)

MySQL

CREATE UNIQUE INDEX some_name ON products (product_no)

Adding a FK Constraint

Microsoft SQL, MySQL, PostgreSQL

ALTER TABLE products ADD CONSTRAINT some_name FOREIGN KEY (group_id) REFERENCES groups (id)

Adding a NotNull Constraint

Microsoft SQL

ALTER TABLE product ALTER COLUMN product_no INT NOT NULL // not full field DDL

MySQL

ALTER TABLE product MODIFY COLUMN product_no INT NOT NULL DEFAULT 0 // full field DDL

PostgreSQL

ALTER TABLE products ALTER COLUMN product_no SET NOT NULL

Removing a NotNull Constraint

Microsoft SQL, MySQL

ALTER TABLE products CHANGE product_no product_no INT NULL

PostgreSQL

ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL

Adding a Primary Key Constraint

Microsoft SQL, PostgreSQL

ALTER TABLE products ADD CONSTRAINT PK_products PRIMARY KEY (id);

MySQL

ALTER TABLE products ADD PRIMARY KEY (id)

Removing a Primary Key Constraint

Microsoft SQL, PostgreSQL

ALTER TABLE products DROP CONSTRAINT PK_products

MySQL

ALTER TABLE products DROP PRIMARY KEY

Set Field as Auto Increment

Microsoft SQL

Not Available

MySQL

ALTER TABLE products CHANGE id id INT NOT NULL AUTO_INCREMENT

PostgreSQL

DECLARE
    seq_name VARCHAR(100);
BEGIN
    SELECT pg_get_serial_sequence('product', 'id') INTO seq_name;
    IF seq_name IS NOT NULL THEN RAISE 'it is already auto increment'; END IF;

    CREATE SEQUENCE seq_product_id;
    SELECT setval('seq_product_id', max(id)) FROM products;
    ALTER TABLE products ALTER COLUMN id DROP DEFAULT;
    ALTER TABLE products ALTER COLUMN id SET DEFAULT NEXTVAL('seq_product_id');
END

Removing Auto Increment

Microsoft SQL

Not Available

MySQL

ALTER TABLE products CHANGE id id INT NOT NULL

PostgreSQL

DECLARE
    seq_name VARCHAR(100);
BEGIN
    SELECT pg_get_serial_sequence('product', 'id') INTO seq_name;
    IF seq_name IS NULL THEN RAISE 'product.id is already NOT auto increment'; END IF;

    ALTER TABLE "product" ALTER COLUMN "id" DROP DEFAULT;
    EXECUTE 'DROP SEQUENCE ""$1""' USING seq_name;
END;

Reading other indexes

Microsoft SQL Server

EXEC sp_helpindex 'products'

MySQL

show index from products

PostgreSQL

select
    t.relname as table_name,
    i.relname as index_name,
    array_to_string(array_agg(a.attname), ', ') as column_names
from
    pg_class t,
    pg_class i,
    pg_index ix,
    pg_attribute a
where
    t.oid = ix.indrelid
    and i.oid = ix.indexrelid
    and a.attrelid = t.oid
    and a.attnum = ANY(ix.indkey)
    and t.relkind = 'r'
    and t.relname like 'products%'
group by
    t.relname,
    i.relname
order by
    t.relname,
    i.relname;

Adding an Index

Microsoft SQL, MySQL, PostgreSQL

CREATE INDEX some_name ON products (product_no [ASC|DESC], ...)

Removing an Index

Microsoft SQL, MySQL

DROP INDEX some_name ON products

PostgreSQL

DROP INDEX some_name