1 / 41

Database Programming

Database Programming. Sections 10 – Constraints. CONSTRAINT TYPES . NOT NULL Constraints UNIQUE Constraints PRIMARY KEY Constraints FOREIGN KEY Constraints CHECK Constraints. Defining CONSTRAINTS. What are constraints? Database rules Constraints always have a name

luke-rivera
Download Presentation

Database Programming

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. Database Programming Sections 10 – Constraints

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

  3. 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

  4. Defining CONSTRAINTS • Two Ways to Define Constraints during Table Creation • Table-Level • NOT NULL cannot be defined at the 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

  5. Naming Constraints • Every constraint has a name. • User defined constraint name, ex. clients_client_num_pk • System named constraint, ex. SYS_C0xxxx • Format table-name_column-name_constraint-type • Limit of 30 characters • If defined at table creation must be named. Marge Hohly

  6. Column Level Constraint • Refers to a single column • CREATE TABLE clients(client_number NUMBER(4) CONSTRAINT clients_client_num_pk PRIMARY KEY,first_name VARCHAR2(14) NOT NULL,last_name VARCHAR2(13)); • Primary key defined at the column level • SYS_Cn (where n is a unique integer) NOT NULL constraint Marge Hohly

  7. Composite key constraint • Composite defined at Table level • CREATE TABLE clients (client_number NUMBER(4) NOT NULL,first_name VARCHAR2(20),last_name VARCHAR2(20)phone VARCHAR2(20)email VARCHAR2(10) NOT NULL,CONSTRAINT clients_phone_email_uk UNIQUE (email,phone)); Marge Hohly

  8. 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

  9. 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

  10. 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

  11. 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

  12. NOT NULL Constraint • Requires that every row has a value for the NOT NULL column • Named with _nn suffix Marge Hohly

  13. UNIQUE constraint • No two rows have the same value • Every value in the column or set of columns (a composite key) are unique • Names with _uk as a suffix • If a composite unique key must be defined at Table level • Column level unique constraint is defined a column level Marge Hohly

  14. Primary Key Constraints • Primary key constraint is a column or set of columns that is uniquely identifies each row in a table • Must satisfy both conditions: • No column that is part of the primary key can contain a null value. • A table can have only one primary key. • Named with _pk suffix Marge Hohly

  15. Primary Key Constraints • CREATE TABLE clients(client_number NUMBER(4) CONSTRAINT clients_client_num_pk PRIMARY KEY,first_name VARCHAR2(14),last_name VARCHAR2(13)); Marge Hohly

  16. Foreign Key(Referential Integrity) constraints • CREATE TABLE clients(client_number NUMBER(4) CONSTRAINT clients_client_num_pk PRIMARY KEY,first_name VARCHAR2(14),last_name VARCHAR2(13),department_id VARCHAR2(4,0),CONSTRAINT clients_dept_id_fk FOREIGH KEY(department_id) REFERENCES departments(department_id)); • Names _fk suffix Marge Hohly

  17. Foreign Key syntax • Column-level • song_id NUMBER(5) CONSTRAINT d_track_list_song_id_fk REFERENCES d_songs(id) • Table-level • CONSTRAINT d_track_list_song_id_fk FOREIGN KEY(song_id) REFERENCES d_songs(id) Marge Hohly

  18. Foreign Key example • Column-level syntax:song_id NUMBER(5) CONSTRAINT d_track_list_song_id_fk REFERENCES d_songs(id) • Table-level syntax:CONSTRAINT d_track_list_song_id_fk FOREIGN KEY(song_id) REFERENCES d_songs(id) Marge Hohly

  19. ON DELETE CASCADE option • Used when defining the foreign key enables the dependent rows in the child table to be deleted when a row in the parent table is deleted. • Without this you can not delete the parent record if there are any rows in the child table with the key value. • Use either on definition of FKON DELETE CASCADE or ON DELETE SET NULL Marge Hohly

  20. ON DELETE CASCADE option • Column-level • song_id NUMBER(5) CONSTRAINT d_track_list_song_id_fk REFERENCES d_songs(id) ON DELETE CASCADE • Table-level • CONSTRAINT d_track_list_song_id_fk FOREIGN KEY(song_id) REFERENCES d_songs(id) ON DELETE CASCADE • Could use ON DELETE SET NULL also Marge Hohly

  21. CHECK constraints • Explicitly defines a condition that must be met. • Condition must be either TRUE or unknown(due to a null) • May refer to values in any column in the table, but not columns in other tables Marge Hohly

  22. CHECK constraint Marge Hohly

  23. Marge Hohly

  24. Marge Hohly

  25. 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

  26. Alter statement requires: • Name of the table • Name of the constraint • Type of constraint • Name of the collumn affected by the constraing • Example: ALTER TABLE d_clientsADD CONSTRAINT clients_client_num_pk PRIMARY KEY(client_number); Marge Hohly

  27. 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

  28. 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

  29. 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

  30. 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

  31. 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

  32. 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

  33. 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

  34. Using the DISABLE clause • Use DISABLE clause with ALTER TABLE or CREATE TABLE statement • ALTER TABLE d_clientsDISABLE CONSTRAINT clients_client_num_pk • CREATE TABLE d_clients(client_number NUMBER(5) PRIMARY KEY DISABLE); Marge Hohly

  35. CASCADE clause • CASCADE clause disables dependent integrity constrains. • If later enabled, the dependent constraints are not automatically enabled • ALTER TABLE table_nameDISABLE CONSTRAINT constraint_name [CASCADE]; • ALTER TABLE d_clientsDISABLE CONSTRAINT clients_client_num_pk CASCADE; Marge Hohly

  36. Enabling Constraints • Use ENABLE clause in the ALTER TABLE statement • ALTER TABLE table_nameENABLE CONSTRAINT constraint_name; • ALTER TABLE d_clientsENABLE CONSTRAINTS clients_client_num_pk; • Can use ENABLE clause in both CREATE TABLE and ALTER TABLE Marge Hohly

  37. Cascading Constraints • Used along with DROP COLUMN clause • Drops all referential-integrity constraints the refer to primary and unique keys defined on the dropped columns • ALTER TABLE table_nameDROP(column name(s)) CASCADE CONSTRAINTS; Marge Hohly

  38. 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

  39. 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

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

  41. Viewing Constraints Marge Hohly

More Related