Difference between revisions of "Oracle:DDL Alter"
m (Protected "Oracle:DDL Alter" ([Edit=Allow only administrators] (indefinite) [Move=Allow only administrators] (indefinite))) |
|
(No difference)
|
Revision as of 16:03, 2 March 2016
Making changes to existing tables
Once a table has been created you may wish to alter it to add new columns, amend the size of existing ones, or drop them completely. This is done using the ALTER TABLE command.
Altering Columns
Existing columns in a table can be altered by using the MODIFY clause:
ALTER TABLE TABLENAME MODIFY (COLUMN DEFINITION);
For example, to alter the BUDGET column of PROJ to accept numbers up to nine digits:
ALTER TABLE PROJ MODIFY (BUDGET NUMBER(9,2));
The system will respond with:
Table altered
Note, there are restrictions on altering an existing column. The width of a column can be decreased and the data type changed, but only if the column contains NULL values. If there is existing data in the column, the only change allowed is to increase the width.
A column can be changed from being NOT NULL to allowing NULL values, by adding the NULL clause at the end of the column specification. For example :
ALTER TABLE PROJ MODIFY (BUDGET NULL);
You can not do the reverse if some of the rows in the column affected contain null values.
Exercise 3.7
Why would this command fail:
ALTER TABLE EMP MODIFY (COMM NOT NULL);
Adding a Column
Extra columns can be added to a table using the ADD clause:
ALTER TABLE TABLENAME ADD (COLUMN DEFINITION)
For example, add a budget column to the DEPT table:
ALTER TABLE DEPT ADD (BUDGET NUMBER(7,2));
If there are no errors, the system will reply with:
Table altered
Add a column MGRPROJNO to the table PROJ (the manager of the project):
ALTER TABLE PROJ ADD (MGRPROJNO NUMBER constraint fk_empmgr REFERENCES EMP(empno));
All fields in the new column will be initially set to NULL. To make a column contain only NON NULL values:
- Add the new column
- Add values to every field, so there are no NULL values
- Alter the table to make the column NOT NULL
A business rule is that all projects must have a manager, so the MGRPROJNO column should really be set to NOT NULL. To do this we need to update all the PROJ records, so they now have a manager.
1. First check how many projects there are:
SELECT * FROM PROJ;
Your results may vary depending on whether you have carried out the previous exercises:
PROJNO PNAME BUDGET MGRPROJNO ---------- -------------------------------- ---------- ---------- 1 ORACLE VERSION 10 15000 2 ORACLE DEVELOPER 10000 3 ORACLE VERSION 12C 8000
2. Assuming you have the above 3 records, update the MGRPROJNO column (amend to match your own data).
Update the PROJ table so that BLAKE is the manager of project 1:
UPDATE proj SET mgrprojno = 7698 WHERE projno = 1;
Update the PROJ table so that JONES is the manager of project 2:
UPDATE PROJ SET mgrprojno = 7566 WHERE projno = 2;
and save the changes so far:
COMMIT;
Exercise 3.8
Update the PROJ table so that CLARK is the manager of project 3.
If you have any other rows in the PROJ table not covered so far, also update these so they have a EMPNO value in the MGRPROJNO column.
3. Now make the column NOT NULL:
ALTER TABLE PROJ MODIFY (MGRPROJNO NOT NULL);
Deleting a Column
A column can be deleted using the DROP clause:
ALTER TABLE table_name DROP COLUMN column_name;
For example, departments are not going to have a budget after all:
ALTER TABLE DEPT DROP COLUMN BUDGET;
Next Step
This completes the DDL section. Now return to the Workbook.