1 / 55

Chapter 4 Constraints

Chapter 4 Constraints. Jason C. H. Chen , Ph.D. Professor of MIS School of Business Gonzaga University Spokane, WA 99258 USA chen@jepson.gonzaga.edu. What will we study today?. Data. Integrity. How to achieve it?. Referential Integrity. …. …. Objectives.

lance
Download Presentation

Chapter 4 Constraints

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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;

More Related