betweenGo

(Photo: Rain on a window in Sunnyvale by basictheory)

There are various ways to alter a table and I usually forget what they are so I am writing this post to remind me.

Columns

ALTER TABLE foo DROP COLUMN nickname;
ALTER TABLE foo RENAME COLUMN name TO nickname;
ALTER TABLE foo ADD name VARCHAR2(254) DEFAULT 'Frank' NOT NULL;
ALTER TABLE foo ADD age INTEGER DEFAULT 0 NOT NULL;
ALTER TABLE foo MODIFY name VARCHAR2(500);

Constraints

ALTER TABLE foo DROP CONSTRAINT foo_a_f;
ALTER TABLE foo ADD CONSTRAINT foo_b_f FOREIGN KEY (bar_id) REFERENCES bar (id);

If you forget the name of a constraint and you can try to find it using this handy piece of SQL.

SELECT constraint_name FROM user_constraints WHERE constraint_name LIKE 'foo_%_f%';

Related posts:

1. HOWTO Alter a Column in a Table
2. Best Practices for Creating Tables
3. Specifying One-to-Many Relationship in ATG Repositories
4. Describing Tables and Constraints in Oracle
5. SQL Insert in One Table Based on Values in Another Table