DATABASE /
SQL ALTER AND DATA MANIPULATION

1. ALTER TABLE STRUCTURE

Modifying the structure of a table.

Always backup the table before making changes.

Not every databases support column deletion, in such case create a new table without that column and copy data.

Upon column deletion all constraints pointing on that column will be dispatched along.

Before deleting a primary key, remove all foreign keys in other tables pointing to this primary key.

After deleting a primary key, you should create a new one in that table.

Along with modifying column name or type, check depending references as well (constraints in the table, foreign keys in other tables).

Constraints usually can not be renamed, use drop-create method instead.

If a foreign key has no name upon defining, it can be found:

• in MySQL: SHOW CREATE TABLE table_name;

• in PostgreSQL: \d table_name

• in SQL Server: EXEC sp_help 'table_name';

If a constraint name is unknown, it can be found:

• in MySQL: SHOW CREATE TABLE table_name;

• in PostgreSQL: \d table_name

• in SQL Server: SELECT name FROM sys.key_constraints WHERE parent_object_id = OBJECT_ID('table_name');

EXAMPLE FOR ADDING KEYS, COLUMN, CONSTRAINTS, DEFAULT VALUE:

EXAMPLE FOR DELETING KEYS, COLUMN, CONSTRAINT, DEFAULT:

EXAMPLE FOR CHANGING PRIMARY KEY:

EXAMPLE FOR RENAMING KEYS, COLUMNS, CONSTRAINTS, CHANGING COLUMN TYPES AND DEFAULT VALUE:

JUMP TO TOP

2. DATA MANIPULATION

Data can be added, modified and deleted from a table as rows.

Always use the WHERE clause when updating and deleting.

In case of large dataset, use LIMIT and execute it the needed times.

Use transactions for safety.

EXAMPLE FOR ADDING, MODIFYING, DELETING DATA:

JUMP TO TOP