Dökümanlar

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