Hints and Tips for ALTERing TABLEs

In ORACLE, the ALTER TABLE construct can be used in two ways: (i) to add a new column (you shouldn't need to do this), or (ii) to change a column's definition. Browse through the online manual to see what is and what is not acceptable. After you define your additional constraints, make sure that your data is still there (in the form it is expected to be), and that no constraints are violated.

Note that you can use the MODIFY clause of this command to change any of the following parts of a column definition:
- Datatype
- Size
- Default value
```
ALTER TABLE accounts
MODIFY (bal  DEFAULT 0)
```
- NOT NULL column constraint
```
ADD (loancode CHAR(1) NOT NULL);
```
Many times you would need to add other constraints to the table. The only type of integrity constraint that you can add to an existing column using the MODIFY clause with the column constraint syntax is a NOT NULL constraint.
However, you can define other types of integrity constraints (UNIQUE, PRIMARY KEY, referential integrity, and CHECK constraints) on existing columns using the ADD clause and the table constraint syntax.
- UNIQUE constraint
```
ALTER TABLE students ADD UNIQUE (ssn);
```
- Referential integrity
```
ALTER TABLE student_courses ADD
(FOREIGN KEY (student_id) REFERENCES student(student_id));
```
To see what your table definition looks like, after issuing such commands, you can use:
```
DESC tablename;
```
This command, however, doesn't seem to print out constraint definitions like KEYs and FOREIGN KEYs. We have found a way out of this, by querying the "system tables" directly. i.e., these are "meta-tables" (or tables about tables) that ORACLE maintains about the schema and nature of information contained in the database. You can't add or delete entries from system tables directly, but can query them to great benefit. So, to find the constraints on a table called TEMP1, you do:
```
SELECT A.constraint_name, A.table_name, A.column_name, B.constraint_type,
B.search_condition, B.r_constraint_name
FROM all_cons_columns A, all_constraints B
WHERE A.constraint_name = B.constraint_name
AND   B.table_name = 'TEMP1';
```
(replace TEMP1 with your table name). In the above example, "all_cons_columns" and "all_constraints" are system tables. Type a DESC on them to see what they are about!