--Alter table: columns: add, delete, rename; change nullness, default, datatype, constraints. --add column. will be Null, so can't be Not Null, unless has default. ALTER TABLE pet ADD (height NUMBER(2)); parens not needed ALTER TABLE pet ADD (height NUMBER(2) DEFAULT 0); ALTER TABLE pet ADD (height NUMBER(2) DEFAULT 0 NOT NULL); --delete a column. all its data deleted ALTER TABLE pet DROP (height); ALTER TABLE pet DROP COLUMN height; --change column name ALTER TABLE pet RENAME COLUMN height TO stature; --modify a column ALTER TABLE pet MODIFY cuteness NOT NULL; --change datatype (column must be empty) ALTER TABLE pet MODIFY height DATE; --can increase size/width of column but can only decrease if no rows or column is all Nulls. --can convert between Char and Varchar if size does not decrease. --can convert between others only if no rows or only Nulls --add Unique constraint. column(s) must be already unique ALTER TABLE pet ADD CONSTRAINT pet_cuteness_unique UNIQUE (cuteness); --add Check constraint. column(s) must be already meet the check ALTER TABLE pet ADD CONSTRAINT pet_cuteness_reasonable CHECK (cuteness BETWEEN 0 AND 100); ALTER TABLE pet ADD CONSTRAINT pet_birth_reasonable CHECK (birth>='1-jan-1978'); --can reference another column (but not at Create Table column constraint (only as table constraint)) ALTER TABLE x ADD CONSTRAINT acons CHECK (columna>columnb); --disable/re-enable a named constraint ALTER TABLE pet DISABLE CONSTRAINT pet_cuteness_reasonable; ALTER TABLE pet ENABLE CONSTRAINT pet_cuteness_reasonable; --remove a named constraint ALTER TABLE pet DROP CONSTRAINT pet_cuteness_reasonable; --add primary key ALTER TABLE event ADD PRIMARY KEY (name,eventdate); ALTER TABLE event ADD CONSTRAINT event_name_eventdate_pk PRIMARY KEY (name,eventdate); --remove primary key ALTER TABLE event DROP PRIMARY KEY; ALTER TABLE event DROP CONSTRAINT event_name_eventdate_pk; --add foreign key ALTER TABLE event ADD FOREIGN KEY (name) REFERENCES pet; ALTER TABLE event ADD CONSTRAINT event_name_fk FOREIGN KEY (name) REFERENCES pet; --what to do if parent row deleted: (default if disallow parent row delete) --delete the children: ALTER TABLE event ADD FOREIGN KEY (name) REFERENCES pet ON DELETE CASCADE; --set children FK column to Null (??what if it ALTER TABLE event ADD FOREIGN KEY (name) REFERENCES pet ON DELETE SET NULL; --remove foreign key (note no Drop Foreign Key, so use named constraint above) ALTER TABLE event DROP CONSTRAINT event_name_fk; --change table name RENAME pet TO companion;