chapter 8 - Referential Integrity p. 285 - Unique constraint alter table sec0803_employees add constraint unique_sec0803_emp_phone_num unique (phone_number); -------------------------------------- p. 286 - Unique index create unique index uix_sec0803b_emp_phone on sec0803b_employees (phone_number); -------------------------------------- p. 287 - Not null alter table sec0804_lunches add constraint nn_sec0804_lunches_employee_id not null (employee_id); -------------------------------------- p. 288 - Primary key alter table sec0805_employees add constraint pk_sec0805_employees primary key (employee_id); -------------------------------------- p. 292 - Setting up Referential Integrity with SQL (Foreign Key) alter table sec0808_clients add constraint fk_sec0808_clients_state_code foreign key (state_code) references sec0808_states (state_code); -------------------------------------- p. 293 - Examples of Referential Integrity Error Additions and changes not allowed a) insert into sec0809_clients values (700, 'Gail Hauser', 'NY'); b) update sec0809_clients set state_code = 'MA' where client_id = 200; -------------------------------------- p. 294 - Examples of additions and updates allowed a) insert into sec0810_clients values (700, 'Gail Hauser', 'OR'); b) update sec0810_clients set state_code = 'WA' where client_id = 200; c) insert into sec0810_clients values (800, 'Carl Logan', null); -------------------------------------- p. 295-6 - Updates and deltes prevented by RI a) invalid update update sec0811_states set state_code = 'MA' where state_code = 'OR'; b) invalid delete delete from sec0811_states where state_code = 'CA'; -------------------------------------- p. 296 - Deleting code from a look-up table a) Set to null the value in dependent table update sec0812_clients set state_code = null where state_code = 'CA'; b) delete code from look-up table delete from sec0812_states where state_code = 'CA'; -------------------------------------- p. 298 - Changing a primary key in a look-up table a) add new code to look-up table insert into sec0813_states values ('ZZ', 'California', 'Sacramento'); b) change old code to new code in dependent table update sec0813_clients set state_code = 'ZZ' where state_code = 'CA'; c) delete old code from look-up table delete from sec0813_states where state_code = 'CA'; -------------------------------------- p. 306 Cascade deletes delete from sec0818_states where state_code = 'CA'; -------------------------------------- p. 309 - Cascade update update sec0819_states set state_code = 'ZZ' where state_code = 'CA'; -------------------------------------- p. 312 - Referential Integrity Variation - using values other than the primary key a) Make column of look-up table unique alter table sec0820_states add constraint unique_sec0820_states_s_capital unique (state_capital) b) Establish foreign key in dependent table alter table sec0820_clients add constraint fk_sec0820_clients_state_capital foreign key (state_Capital) references sec0820_states (state_capital); -------------------------------------- p. 314 Multiple columns in a foreign key alter table sec0821_lunch_items add constraint fk_sec0821_lunch_items_foods foreign key (supplier_id, product_code) references sec0821_foods (supplier_id, product_code); -------------------------------------- p. 315 Look-up and data table the same alter table sec0822_employees add constraint fk_sec0822_emp_manager_id foreign key (manager_id) references sec0822_employees (employee_id); -------------------------------------- p. 317-318 Codidng constraints in the create statement a) without naming constraints create table sec0823a_employees (employee_id integer primary key, first_name varchar(10) not null, last_name varchar(20) not null, dept_code varchar(3) references sec0823a_departments(dept_code), hire_date datetime, credit_limit money, phone_number varchar(4) unique, manager_id integer references sec0823a_employees(employee_id), unique (first_name, last_name) ); b) with constraint naming create table sec0823b_employees (employee_id integer constraint pk_employees primary key, first_name varchar(10) constraint nn_first_name not null, last_name varchar(20) constraint nn_last_name not null, dept_code varchar(3) constraint fkb_dept_code references sec0823b_departments(dept_code), hire_date datetime, credit_limit money, phone_number varchar(4) constraint unique_phone_num unique, manager_id integer constraint fk_manager references sec0823b_employees(employee_id), constraint unique_full_name unique (first_name, last_name) ); --------------------------------------