Using DDL Statements to Create and Manage Tables

1. Using DDL Statementsto Create and Manage Tables

2. Objectives After completing this lesson, you should be able to do the following: • Describe the main database objects • Create tables • Describe the data types that can be used when specifying column definition • Alter table definitiond • Drop, and truncate tables • Describe constraints • Create and maintain constraints

3. SQL Statements • SELECT • INSERT • UPDATE • DELETE • CREATE • ALTER • DROP • RENAME • TRUNCATE • COMMIT • ROLLBACK • SAVEPOINT • GRANT • REVOKE Data retrieval Data manipulation language (DML) Data definition language (DDL) Transaction control Data control language (DCL)

4. Database Objects

5. Naming Rules Table names and column names: • Must begin with a letter • Must be 1–30 characters long • Must contain only A–Z, a–z, 0–9, _, $, and # • Must not duplicate the name of another object owned by the same user • Must not be an Oracle server reserved word

6. Creating Tables

7. CREATE TABLE Statement • You must have: • CREATE TABLE privilege • A storage area • You specify: • Table name • Column name, column data type, and column size CREATE TABLE [schema.]table (columndatatype [DEFAULT expr][, ...]);

8. USERA USERB SELECT * FROM userB.employees; SELECT * FROM userA.employees; Referencing Another User’s Tables • Tables belonging to other users are not in the user’s schema. • You should use the owner’s name as a prefix to those tables.

9. DEFAULT Option • Specify a default value for a column during an insert. • literal values, expressions, or SQL functions are legal values. • Another column’s name or a pseudocolumn are illegal values. • The default data type must match the column data type. ... hire_date DATE DEFAULT SYSDATE, ... CREATE TABLE hire_dates (id NUMBER(8), hire_date DATE DEFAULT SYSDATE);

10. CREATE TABLE dept (deptno NUMBER(2), dname VARCHAR2(14), loc VARCHAR2(13), create_date DATE DEFAULT SYSDATE); Creating Tables • Create the table. • Confirm table creation. DESCRIBE dept

11. Tables in Oracle Database • User Tables(created and maintained by the user, contain user information) • Data dictionary(created and maintained by the oracle service, contain DB information)

12. Data Dictionary Structure View naming convention:

13. Querying the Data Dictionary select * from user_tables • Describe tables owned by the user • View distinct object types owned by the user • View tables, views, synonyms, and sequences owned by the user select distinct object_type from user_objects select * from user_catalog

14. Data Types

15. Creating a Tableby Using a Subquery • Create a table and insert rows by combining the CREATE TABLE statement and the ASsubquery option. • Match the number of specified columns to the number of subquery columns. • Define columns with column names anddefault values. CREATE TABLE table [(column, column...)] AS subquery;

16. Creating a Tableby Using a Subquery CREATE TABLE dept80 AS SELECT employee_id, last_name, salary*12 ANNSAL, hire_date FROM employees WHERE department_id = 80; DESCRIBE dept80

17. Alter Table

18. ALTER TABLE Statement Use the ALTERTABLE statement to: • Add a new column • Modify an existing column • Define a default value for the new column ALTER TABLE table ADD (column datatybe [DEFAULT expr] { ,column datatybe]….); ALTER TABLE table MODIFY (column datatybe [DEFAULT expr] { ,column datatybe]….);

19. “…add a newcolumn intoDEPT30 table…” JOB Adding a Column DEPT30 New column EMPNO ENAME ANNSAL HIREDATE ------ ---------- -------- 7698 BLAKE 34200 01-MAY-81 7654 MARTIN 15000 28-SEP-81 7499 ALLEN 19200 20-FEB-81 7844 TURNER 18000 08-SEP-81 ... JOB DEPT30 EMPNO ENAME ANNSAL HIREDATE ------ ---------- -------- 7698 BLAKE 34200 01-MAY-81 7654 MARTIN 15000 28-SEP-81 7499 ALLEN 19200 20-FEB-81 7844 TURNER 18000 08-SEP-81 ...

20. Adding a Column • You use the ADD clause to add columns • The new column become the last column ALTER TABLE dept ADD (job varchar2(9));

21. Adding a Column • You use the ADD clause to add columns. ALTER TABLE dept30 ADD (job VARCHAR2(9)); Table altered. • The new column becomes the last column. EMPNO ENAME ANNSAL HIREDATE JOB --------- ---------- --------- --------- ---- 7698 BLAKE 34200 01-MAY-81 7654 MARTIN 15000 28-SEP-81 7499 ALLEN 19200 20-FEB-81 7844 TURNER 18000 08-SEP-81 ... 6 rows selected.

22. Modifying a Column • You can change a column’s datatype, size, and default value • A change to the default value effects only subsequent insertions to the table ALTER TABLE dept MODIFY (dname varchar2(18));

23. Modifying a Column • You can change a column's datatype, size, and default value. You can change a column's name. . ALTER TABLE dept30 MODIFY (ename VARCHAR2(15)); Table altered. ALTER TABLE dept30 Rename column ename to employee_name; Table altered

24. Drop Table

25. Dropping a Table • All data and structure in the table are deleted. • Any pending transactions are committed. • All indexes are dropped. • You cannot roll back the DROP TABLE statement. DROP TABLE dept80;

26. Changing ObjectName (Table)

27. Changing the Name of an Object • To change the name of a table, view, sequence, or synonym, you execute the RENAME statement. • You must be the owner of the object. RENAME dept TO department; Table renamed.

28. Truncating Tables

29. TRUNCATE Statement • Removes all rows from a table, leaving the table empty and the table structure intact • Release the storage space used by the table • Syntax: • Example: • You cannot roll back row removal when using TRUNCATE • Alternatively, you can remove rows by using the DELETE statment TRUNCATE TABLE table_name; TRUNCATE TABLE dept;

30. Summary

31. Practice Overview • Creating new tables • Creating a new table by using the CREATE TABLE AS syntax • Modifying column definitions • Verifying that the tables exist • Dropping tables • Altering tables

32. Constraints

33. Objectives After completing this lesson, you should be able to do the following: • Describe constraints • Create and maintain constraints

34. What Are Constraints? • Constraints enforce rules at the table level. • Constraints prevent the deletion of a table if there are dependencies. • The following constraint types are valid in Oracle: • NOT NULL • UNIQUE • PRIMARY KEY • FOREIGN KEY • CHECK

35. Data Integrity Constraints

36. Constraint Guidelines • You can name a constraint, or the Oracle server generates a name by using the SYS_Cnformat. • Create a constraint at either of the following times: • At the same time as the table is created • After the table has been created • Define a constraint at the column or table level. • View a constraint in the data dictionary.

37. Defining Constraints • Syntax: • Example: CREATE TABLE employees( employee_id NUMBER(6) CONSTRAINT emp_emp_id_pk PRIMARY KEY, first_name VARCHAR2(20)); CREATE TABLE [schema.]table (columndatatype [DEFAULT expr] [column_constraint], ... [table_constraint][,...]);

38. Defining Constraints column[CONSTRAINT constraint_name] constraint_type, • Column-level constraint: • Table-level constraint: column,... [CONSTRAINT constraint_name] constraint_type (column, ...),

39. Defining Constraints CREATE TABLE employees( employee_id NUMBER(6) CONSTRAINT emp_emp_id_pk PRIMARY KEY, first_name VARCHAR2(20)); • Column-level constraint: • Table-level constraint: 1 CREATE TABLE employees( employee_id NUMBER(6), first_name VARCHAR2(20), ... job_id VARCHAR2(10) NOT NULL, CONSTRAINT emp_emp_id_pk PRIMARY KEY (EMPLOYEE_ID)); 2

40. NOT NULL Constraint Ensures that null values are not permitted for the column: … NOT NULL constraint (No row can containa null value forthis column.) NOT NULL constraint Absence of NOT NULL constraint (Any row can contain a null value for this column.)

41. The NOT NULL Constraint • Defined at the column level CREATE TABLE employees( employee_id NUMBER(6), last_name VARCHAR2(25) NOT NULL, email VARCHAR2(25), salary NUMBER(8,2), commission_pct NUMBER(2,2), hire_date DATE NOT NULL);

42. The UNIQUE Key Constraint UNIQUE constraint EMPLOYEES … INSERT INTO Allowed Not allowed: already exists

43. Insert into Not allowed (DNAME-SALES already exists) 50 SALES DETROIT 60 BOSTON Allowed The UNIQUE Key Constraint UNIQUE key constraint DEPT DEPTNO DNAME LOC ------ ---------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON

44. The UNIQUE Key Constraint Defined at either the table level or the column level: CREATE TABLE employees( employee_id NUMBER(6), last_name VARCHAR2(25) NOT NULL, email VARCHAR2(25), salary NUMBER(8,2), commission_pct NUMBER(2,2), hire_date DATE NOT NULL, CONSTRAINT emp_email_uk UNIQUE(email));

45. PRIMARY KEY Constraint DEPARTMENTS PRIMARY KEY … Not allowed (null value) INSERT INTO Not allowed (50 already exists)

46. The PRIMARY KEY Constraint Defined at either the table level or the column level CREATE TABLE employees( employee_id NUMBER(6), last_name VARCHAR2(25) NOT NULL, email VARCHAR2(25), salary NUMBER(8,2), commission_pct NUMBER(2,2), hire_date DATE NOT NULL, CONSTRAINT emp_empid_pk PRIMARY KEY(employee_id));

47. FOREIGN KEY Constraint DEPARTMENTS PRIMARYKEY … EMPLOYEES FOREIGNKEY … Not allowed(9 does not exist) INSERT INTO Allowed

48. FOREIGN KEY Constraint Defined at either the table level or the column level: CREATE TABLE employees( employee_id NUMBER(6), last_name VARCHAR2(25) NOT NULL, email VARCHAR2(25), salary NUMBER(8,2), commission_pct NUMBER(2,2), hire_date DATE NOT NULL, ... department_id NUMBER(4), CONSTRAINT emp_dept_fk FOREIGN KEY (department_id) REFERENCES departments(department_id), CONSTRAINT emp_email_uk UNIQUE(email));

49. FOREIGN KEY Constraint:Keywords • FOREIGN KEY: Defines the column in the child table at the table-constraint level • REFERENCES: Identifies the table and column in the parent table • ON DELETE CASCADE: Deletes the dependent rows in the child table when a row in the parent table is deleted • ON DELETE SET NULL: Converts dependent foreign key values to null

50. CHECK Constraint • Defines a condition that each row must satisfy • The following expressions are not allowed: • References to CURRVAL, NEXTVAL, LEVEL, and ROWNUM pseudocolumns • Calls to SYSDATE, UID, USER, and USERENV functions • Queries that refer to other values in other rows ..., salary NUMBER(2) CONSTRAINT emp_salary_min CHECK (salary > 0),...