Database Programming

1. Database Programming Sections 9 & 10 – DDL Data Definition Language,

2. Data Definition Language DDL • Data Definition Language DDL • ALTER TABLE • DROP TABLE • RENAME • TRUNCATE • COMMENT Marge Hohly

3. ALTER TABLE • Use ALTER TABLE to: • ADD a new column (Always goes at the end of the table) • MODIFY an existing column (Change data type, size, or default value – see restrictions) • Define a DEFAULT value for a column • DROP COLUMN (Cannot drop all columns) • SET UNUSED/DROP UNUSED Marge Hohly

4. ALTER TABLE copy_items ADD(price number(8)); ALTER TABLE copy_items MODIFY(price number(7)); ALTER TABLE copy_items DROP COLUMN price; adding a new column modifying an existing column width, datatype, default value only affects subsequent insertions dropping a column – one at a time must have at least one column left ALTER TABLE Examples Marge Hohly

5. ALTER TABLE copy_itemsSET UNUSED (qty_on_hand); ALTER TABLE copy_itemsDROP UNUSED COLUMNS; set unused so it can be dropped later – no access once set unused reclaim disk space ALTER TABLE Examples cont’d Marge Hohly

6. TRUNCATE • Removes all rows in a table • Releases storage space where as DROP does not release storage space • Example:TRUNCATE TABLE copy_items; Marge Hohly

7. COMMENT ON TABLE • Table Example:COMMENT ON TABLE itemsIS ‘Inventory items begin with a default qty_on_hand of zero’;View this comment using:SELECT * FROM USER_TAB_COMMENTS; • Column Example:COMMENT ON COLUMN copy_items.qty_on_handIS ‘begin with a default value of one’;View this comment usingDESC copy_items; Marge Hohly

8. CONSTRAINT TYPES • NOT NULL Constraints • UNIQUE Constraints • PRIMARY KEY Constraints • FOREIGN KEY Constraints • CHECK Constraints Marge Hohly

9. Defining CONSTRAINTS • What are constraints? • Database rules • Constraints always have a name • Given by you/DBA when constraint is created (preferred method because names are meaningful) • Given by the system when constraint is created (names are not meaningful) Marge Hohly

10. Defining CONSTRAINTS • Two Ways to Define Constraints during Table Creation • Table-Level • If the word CONSTRAINT is used in the CREATE TABLE statement, the constraint must be given a name • Composite-key constraints must be defined at the table-level • Column-Level • NOT NULL must be defined at the Column-Level Marge Hohly

11. Defining CONSTRAINTS • Table-Level Constraints – at the bottom EXAMPLE:CREATE TABLE copy_employees(employee_id NUMBER(6),first_name VARCHAR2(20),job_id VARCHAR2(10),CONSTRAINT cemp_emp_id_pk PRIMARY KEY(employee_id), CONSTRAINT cemp_job_id_fk FOREIGN KEY(job_id) REFERENCES jobs(job_id),CONSTRAINT cemp_first_name_uk UNIQUE (first_name),CONSTRAINT cemp_emp_id_ck CHECK (employee_id<=999999)); Note: The words “Foreign Key” are Used at the table level Marge Hohly

12. NAMING at TABLE LEVEL • Constraint naming format table_col_type • CONSTRAINT constraint_name TYPE OFCONSTRAINT(column_name) • CONSTRAINT cemp_emp_id_pk PRIMARYKEY(employee_id) • CONSTRAINT cemp_emp_id_lname_pk PRIMARYKEY(employee_id,last_name) • CONSTRAINT constraint_name TYPE OFCONSTRAINT(column_name)REFERENCES othertablename(column_name) • CONSTRAINT cemp_job_id_fk FOREIGNKEY(job_id)REFERENCES copy_jobs(job_id), Marge Hohly

13. NAMING at COLUMN LEVEL • Column Level Assigning A Constraint Name: • System Named: • column_name datatype() TYPE OF CONSTRAINT • employee_id NUMBER(6) PRIMARY KEY • User Named: • column_name datatype() CONSTRAINT constraint name TYPE OF CONSTRAINT • employee_id NUMBER(6) CONSTRAINT c2emp_emp_id_pk PRIMARY KEY • Foreign Key: • column_name datatype() CONSTRAINT constraint_name TYPE OF CONSTRAINT (column it is on) REFERENCES othertablename(column_name) Marge Hohly

14. Defining CONSTRAINTS • Column-Level Constraints • Example:CREATE TABLE copy2_employees(employee_id NUMBER(6) CONSTRAINT c2emp_emp_id_pk PRIMARY KEY,CONSTRAINT c2emp_emp_id_ck CHECK(employee_id<=999999),first_name VARCHAR2(20)CONSTRAINT c2emp_first_name_nn NOT NULL,last_name VARCHAR2(20)CONSTRAINT c2emp_last_name_nn NOT NULL,address VARCHAR2(20) CONSTRAINT c2emp_address_ck NOT NULL,job_id VARCHAR2(10)CONSTRAINT c2emp_job_id_fk REFERENCES copy_jobs(job_id)); Marge Hohly

15. Adding Constraints AFTER Table is created: • First, create a table that does not already have constraints: • CREATE TABLE copy3_employees(employee_id NUMBER(6),first_name VARCHAR2(20),last_name VARCHAR2(20),department_id NUMBER(4)); Marge Hohly

16. Adding Constraints AFTER Table is created: • Secondly, add the constraints:ALTER TABLE copy3_employeesADD CONSTRAINT emp3_emp_id_pkPRIMARY KEY(employee_id);ALTER TABLE copy3_employeesADD CONSTRAINT emp3_emp_id_fkFOREIGN KEY(department_id)REFERENCES copy_departments(department_id); Marge Hohly

17. Adding Constraints AFTER Table is created: • NOTE!!! For NOT NULL constraints, use the MODIFY keyword in the ALTER TABLE statement instead of ADD • ALTER TABLE copy3_employeesMODIFY (first_name CONSTRAINT emp3_first_name_nn NOT NULL); • NOT NULL constraints can only be added if the column does not already contain null values Marge Hohly

18. Miscellaneous Constraint Information ... • If the word CONSTRAINT is used in a CREATE TABLE statement, the constraint must be given a name • Constraints that contain more than one column are called composite key constraints and must be specified at the table level by placing a comma between the column names • There is no limit to the number of CHECK CONSTRAINTS that can be specified for a column Marge Hohly

19. Miscellaneous FK Constraints Information... • Another name for FOREIGN KEY CONSTRAINTS is REFERENCIAL INTEGRITY CONSTRAINTS • When specifying FOREIGN KEY CONSTRAINTS, the table that contains the PRIMARY KEY is called the PARENT TABLE. The table that contains the FOREIGN KEY CONSTRAINT is called the CHILD TABLE. Marge Hohly

20. DISABLING CONSTRAINTS • Constraints can be disabled • Examples: • ALTER TABLE copy3_employeesDISABLE CONSTRAINTemp3_emp_id_pk; • ALTER TABLE copy3_employeesDISABLE CONSTRAINTemp3_emp_id_pk CASCADE; • This will cause any FOREIGN KEY that references this primary key to also be disabled. Marge Hohly

21. ENABLING CONSTRAINTS • EXAMPLES: • ALTER TABLE copy3_employeesENABLE CONSTRAINT emp3_emp_id_pk • Note: This does not enable the foreign key in the child tables Marge Hohly

22. DROPPING CONSTRAINTS • Examples: • ALTER TABLE table_nameDROP CONSTRAINT TYPE (column_name)[CASCADE]; • ALTER TABLE table_nameDROP CONSTRAINT name[CASCADE]; • ALTER TABLE c_clientsDROP PRIMARY KEY CASCADE; Marge Hohly

23. Viewing Constraint • Use the DESCRIBE command to confirm its existence . • DESCRIBE can only verify is the NOT NULL constraint. • NOT NULL constraint appears in the data dictionary as a CHECK constraint. • Use a query of the USER_CONSTRAINTS table to view all constraints on your table. • SELECT constraint_name, constraint_typeFROM user_constraintsWHERE TABLE_NAME ='table_name'; • SELECT constraint_name, constraint_type FROM user_constraintsWHERE TABLE_NAME ='COPY3_EMPLOYEES‘; Marge Hohly

24. QUERY THE DATA DICTIONARY • SELECT constraint_name, constraint_type, table_name, statusFROM user_constraints; • Types: • P = Primary Key • R = Foreign Key (Referential) • C = Check (Includes NOT NULL) • U = Unique Marge Hohly

25. Viewing Constraint • SELECT constraint_name, column_nameFROM user_cons_columnsWHERE table_name = 'EMPLOYEES'; Marge Hohly

26. Viewing Constraints Marge Hohly