420 likes | 627 Views
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
E N D
Database Programming Sections 10 – Constraints
CONSTRAINT TYPES • NOT NULL Constraints • UNIQUE Constraints • PRIMARY KEY Constraints • FOREIGN KEY Constraints • CHECK Constraints Marge Hohly
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
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
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
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
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
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
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
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
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
NOT NULL Constraint • Requires that every row has a value for the NOT NULL column • Named with _nn suffix Marge Hohly
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
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
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
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
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
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
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
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
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
CHECK constraint Marge Hohly
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Viewing Constraint • SELECT constraint_name, column_nameFROM user_cons_columnsWHERE table_name = 'EMPLOYEES'; Marge Hohly
Viewing Constraints Marge Hohly