Chapter 4 Constraints

1. Chapter 4Constraints Jason C. H. Chen, Ph.D. Professor of MIS School of Business Gonzaga University Spokane, WA 99258 USA chen@jepson.gonzaga.edu

2. What will we study today? Data Integrity How to achieve it? Referential Integrity … …

3. Objectives • Explain the purpose of constraints in a table • Distinguish among PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, and NOT NULL constraints and the appropriate use for each constraint • Understand how constraints can be created when creating a table or modifying an existing table • Distinguish between creating constraints at the column level and table level

4. Objectives (continued) • Create PRIMARY KEY constraints for a single column and a composite primary key (cpk) • Create a FOREIGN KEY constraint • Create a UNIQUE constraint • Create a CHECK constraint • Create a NOT NULL constraint using the ALTER TABLE…MODIFY command • Include constraints during table creation • Use DISABLE and ENABLE commands • Use the DROP command

5. Referential Integrity Customers#5 Can we “delete” customers#5 if orders#1 is still in the database? Why? customers pk orders#1 orders fk pk Can we “create” orders#1 if customers#5 is not created? Why?

6. Refresh the Database • 1. Create a new folder on c:\ as follows: c:\oradata\chapter4 • 2. Go to Blackboard and download data files from Oracle chapter4 and save under c:\oradata\chapter4\ • 3. Run the following script file • Start c:\oradata\chapter4\JLDB_Build_4.sql

7. Type the following SQL commands -- chapter 4, Figure 4-5; p. 104 INSERT INTO customers (customer#, lastname, firstname, region) VALUES (1020, 'PADDY', 'JACK', 'NE'); --extra INSERT command INSERT INTO orders (order#, customer#, orderdate) VALUES (1021, 1021, '06-APR-09'); -- DELETE command DELETE FROM customers WHERE customer# = 1005;

8. pk Customers#5 Can we “delete” customers#5 if orders#1 is still in the database? Why? Can we insert a different customer with “Duplicate” pk? pk fk orders#1 Can we “create” orders#1 if customers#5 is not created? Why?

9. customers pk orders fk pk

10. Review from Last Class What are the three rules of naming table and field? • What are the three (total of four) required information that should be described for each field? • Name • Type • Size • Constraint L

11. What is a Constraint? • A rule used to enforce business rules, practices, and policies • A rule used to ensure accuracy and integrity of data • A mechanism used to protect • the relationship between data within an Oracle table, or • the correspondence between data in two different tables. • For example, the state entered must be one of the 50 states in the U.S.

12. Types of Constraints • Integrity constraints: define primary and foreign keys • Value constraints: define specific data values or data ranges that must be inserted into columns and whether values must be unique or not NULL • Table constraint: restricts the data value with respect to all other values in the table • Field (column) constraint: limits the value that can be placed in a specific field, irrespective of values that exist in other table records

13. Integraity constraint Value constraint I. Naming conventions for constraints <tablename>_<fieldname>_<constraint id> Where <constraint id> is: • pk PRIMARY KEY • fk REFERENCES <tablename> (pk) • ck CHECK <condition to be checked> (note that cc stands for CHECK CONDITION) • nn NOT NULL • uk UNIQUE e.g., s_id NUMBER (6) CONSTRAINT student_s_id_pkPRIMARY KEY;

14. Integrity Constraints • Define primary key fields • Specify foreign keys and their corresponding table and column references • Specify composite keys

15. Creating a Table CREATE TABLE tablename (fieldname1 data_type (size) [CONSTRAINT constraint_name constraint_type], fieldname2 data_type (size), … [CONSTRAINT constraint_name constraint_type,] …);

16. Creating Constraints • When • During table creation • After table creation, by modifying the existing table • How • Column level approach • Table level approach

17. Creating Constraints at the Column Level • If a constraint is being created at the column level, the constraint applies to the column specified Figure 4-1 Syntax for creating a column-level constraint Creating Constraints at the Table Level • Approach can be used to create any constraint type after all table field definitions are completed except NOT NULL • Required if constraint is based on multiple columns Figure 4-2 Syntax for creating a table-level constraint

18. pk CUSTOMERS pk fk ORDERS CREATE TABLE Customers (Customer# NUMBER(4), LastName VARCHAR2(10) NOT NULL, FirstName VARCHAR2(10) NOT NULL, Address VARCHAR2(20), City VARCHAR2(12), State VARCHAR2(2), Zip VARCHAR2(5), Referred NUMBER(4), Region CHAR(2), Email VARCHAR2(30), CONSTRAINT customers_customer#_pk PRIMARY KEY(customer#), CONSTRAINT customers_region_ck CHECK (region IN ('N', 'NW', 'NE', 'S', 'SE', 'SW', 'W', 'E')) ); Optional (variable name)

19. Qs • Q: How to display (describe) the table structure you just created of “customers” table? • A: _______________________ • Q: Any constraint(s) displayed? • A: ____ • Q: How to display them? • A:_ DESCRIBE customers; case is sensitive NO! SELECT constraint_name FROM user_constraints WHERE table_name=‘CUSTOMERS’;

20. pk CUSTOMERS pk ORDERS fk CREATE TABLE Orders (Order# NUMBER(4), Customer# NUMBER(4), OrderDate DATE NOT NULL, ShipDate DATE, ShipStreet VARCHAR2(18), ShipCity VARCHAR2(15), ShipState VARCHAR2(2), ShipZip VARCHAR2(5), ShipCost NUMBER(4,2), CONSTRAINT orders_order#_pkPRIMARY KEY(order#), CONSTRAINT orders_customer#_fkFOREIGN KEY (customer#) REFERENCES customers(customer#)); Optional (variable name)

21. How to define ‘composite” key? pk ORDERS pk BOOKS cpk fk fk ORDERITEMS CREATE TABLE ORDERITEMS ( Order# NUMBER(4), Item# NUMBER(2), ISBN VARCHAR2(10), Quantity NUMBER(3) NOT NULL, PaidEach NUMBER(5,2) NOT NULL, CONSTRAINT orderitems_order#item#_pkPRIMARY KEY (order#, item#), CONSTRAINT orderitems_order#_fkFOREIGN KEY (order#) REFERENCES orders (order#) , CONSTRAINT orderitems_isbn_fkFOREIGN KEY (isbn) REFERENCES books (isbn) , CONSTRAINT oderitems_quantity_ckCHECK (quantity > 0) ); Optional (variable name)

22. Your Job • You need to study and understand all CREATE TABLE SQL commands in JLDB_Build_4.sql

23. Enforcement of Constraints • All constraints are enforced at the table level • If a data value violates a constraint, the entire row is rejected

24. Constraint Types Table 4-1 Constraint types

25. Your Turn …

26. Primary Key Constraints • Syntax: CONSTRAINT constraint_name PRIMARY KEY • Create a table with the following information: • Name of the table: students • Fields: s_id number with 6 digits and is a primary key, s_name character with 30 chars, s_class with 2 chars, s_dob with DATE • SQL> CREATE TABLE students • (s_id NUMBER(6) CONSTRAINT students_s_id_pk PRIMARY • KEY, • 3 s_name VARCHAR2(30), • s_class CHAR(2), • s_dob DATE);

27. Primary Key Constraints (cont.) • at the Column-Level • SQL> CREATE TABLE students • (s_id NUMBER(6) CONSTRAINT students_s_id_pkPRIMARY • KEY, • 3 s_name VARCHAR2(30), • s_class CHAR(2), • s_dob DATE); Practice: Type in one of the command. • at the Table-Level • SQL> CREATE TABLE students • 2 (s_id NUMBER(6), • 3 s_name VARCHAR2(30), • s_class CHAR(2), • s_dob DATE, • CONSTRAINT students_s_id_pkPRIMARY KEY (s_id));

28. Adding Constraints to Existing Tables • Constraints are added to an existing table with the ALTER TABLE command • Add a NOT NULL constraint using MODIFY clause • All other constraints are added using ADD clause

29. Using the PRIMARY KEY Constraint • Ensures that columns do not contain duplicate or NULL values • Only one per table is allowed Figure 4-3 Syntax of the ALTER TABLE command to add a PRIMARY KEY constraint

30. Constraint Checked with Data Input Figure 4-5 Insert a row to test the constraint

31. PRIMARY KEY Constraint for Composite Key • List column names within parentheses separated by commas Figure 4-7 Adding a composite PRIMARY KEY constraint

32. Drop Contraint ALTER TABLE orderitems DROP CONSTRAINT orderitems_order#item#_pk PRIMARY KEY (order#, item#);

33. Multiple Constraints on a Single Column • A column may be included in multiple constraints • The order# column is included in a primary key and a foreign key constraint Optional (variable name) Figure 4-32 Assigning multiple constraints to a column

34. Using the FOREIGN KEY Constraint • Requires a value to exist in the referenced column of another table • NULL values are allowed • Enforces referential integrity • Maps to the PRIMARY KEY in parent table customers pk orders fk

35. Using the FOREIGN KEY Constraint • You cannot delete a value in a parent table (pk) referenced by a row in a child table (fk) customers pk orders fk

36. customers pk orders fk

37. FOREIGN KEY Constraint Example Figure 4-9 Adding a FOREIGN KEY constraint

38. Deletion of Foreign Key Values • You cannot delete a value in a parent table referenced by a row in a child table • Use ON DELETE CASCADE keywords when creating FOREIGN KEY constraint – it automatically deletes a parent row when the row in a child table is deleted

39. Using the UNIQUE Constraint • No duplicates are allowed in the referenced column • NULL values are permitted Figure 4-16 Adding a UNIQUE constraint

40. Using the CHECK Constraint • Updates and additions must meet specified condition Figure 4-19 Adding a CHECK constraint to the ORDERS table

41. Using the NOT NULL Constraint • The NOT NULL constraint is a special CHECK constraint with IS NOT NULL condition • Can only be created at column level • Included in output of DESCRIBE command • Can only be added to an existing table using ALTER TABLE…MODIFY command

42. NOT NULL Constraint Example Figure 4-23 Adding a NOT NULL constraint

43. EQUIP DEPT ETYPES EquipID Edesc Purchdate Rating DeptID EtypeID DeptID Dname Fax EtypeID Etypename Practice … • Let’s try to create additional tables • JustLee Books would like to create some new tables to store office equipment inventory data. Figure 4-26 E-R model for equipment tables

44. EQUIP DEPT ETYPES EquipID Edesc Purchdate Rating DeptID EtypeID DeptID Dname Fax EtypeID Etypename Including Constraints during Table pk pk pk • Each department name must be unique. • Each department must be assigned a name . fk fk unique NOT NULL • Each equipment type name must be unique • Each equipment type must be assigned a name. • Each equipment item must be assigned a valid department. • If an equipment item is assigned a type, it must be a valid type. • Valid rating values for equipment are A, B, and C. unique NOT NULL ck

45. DEPT table creation -- chapter 4, Figure 4-27; p. 117 CREATE TABLE dept (deptid NUMBER(2), dname VARCHAR2(20) NOT NULL, fax VARCHAR2(12), CONSTRAINT dept_deptid_pk PRIMARY KEY (deptid), CONSTRAINT dept_dname_uk UNIQUE (dname) ); • Each department name must be unique. • Each department must be assigned a name . unique NOT NULL

46. What is the main difference on the following “CREATE TABLE” statements? Constraints are defined at the table-level -- chapter 4, Figure 4-27; p. 117 CREATE TABLE dept (deptid NUMBER(2), dname VARCHAR2(20) NOT NULL, fax VARCHAR2(12), CONSTRAINT dept_deptid_pk PRIMARY KEY (deptid), CONSTRAINT dept_dname_uk UNIQUE (dname) ); Constraints are defined at the column-level -- chapter 4, Figure 4-30; p. 119 CREATE TABLE dept (deptid NUMBER(2) CONSTRAINT dept_deptid_pk PRIMARY KEY, dname VARCHAR2(20) NOT NULL CONSTRAINT dept_dname_uk UNIQUE, fax VARCHAR2(12));

47. -- chapter 4, Figure 4-28; p. 118 CREATE TABLE etypes (etypeid NUMBER(2), etypename VARCHAR2(20) NOT NULL, CONSTRAINT etypes_etypeid_pk PRIMARY KEY (etypeid), CONSTRAINT etypes_etypename_uk UNIQUE (etypename) ); • Each equipment type name must be unique • Each equipment type must be assigned a name. -- chapter 4, Figure 4-29; p. 119 CREATE TABLE equip (equipid NUMBER(3), edesc VARCHAR2(30), purchdate DATE, rating CHAR(1), deptid NUMBER(2) NOT NULL, etypeid NUMBER(2), CONSTRAINT equip_equipid_pk PRIMARY KEY (equipid), CONSTRAINT equip_deptid_fk FOREIGN KEY (deptid) REFERENCES dept (dept_id), CONSTRAINT equip_etypeid_fk FOREIGN KEY (etypeid) REFERENCES etypes (etypeid), CONSTRAINT equip_rating_ck CHECK (rating IN ('A', 'B', 'C', 'D')) ); • Each equipment item must be assigned a valid department. • If an equipment item is assigned a type, it must be a valid type. • Valid rating values for equipment are A, B, and C.

48. Including Constraints during Table Creation – Table Level • Include at end of column list Figure 4-29 EQUIP table creation

49. Viewing Constraints – USER_CONSTRAINTS • Display constraint listing for a specific table Figure 4-33 SELECT statement to view data about existing constraints

50. Disabling Constraints -- chapter 4, Figure 4-36; p. 123 ALTER TABLE equip DISABLE CONSTRAINT equip_rating_ck; ALTER TABLE equip ENABLE CONSTRAINT equip_rating_ck;