06 Relational DBMS

1. And Franchise Colleges DATABASE DEVELOPMENT 06 Relational DBMS • By MANSHA NAWAZ Section 06

2. Overview In this lecture we well be looking at: • Entity Integrity. • Referential Integrity. • Discuss the effect UPDATE, DELETE and INSERT can have on Referential Integrity. Section 06

3. Entity Integrity • Example: STUDENT (STUDENT# , NAME , …) • student# is the primary key • A particular student#, e.g. 'S4', can only occur once in that column of the table. • Each row in the table STUDENT represents a real-world entity. • The first row [S4, Ramesh] implies that there is a student somewhere identified by 'S4' and named 'Ramesh'. • If each row represents an real-world entity we must be able to differentiate between them. • Would you allow rows like this in a database? Section 06

4. Entity Integrity continued …. • Definition of Entity Integrity Entity Integrity requires that every attribute that participates in a primary key is not allowed to have a null (empty) value. • Example: • This example breaks Entity Integrity • The missing ISBNX number is only part of the primary key but the identity of the row is still lost. • The table is a list of reservations by borrowers of particular books. • The row with the missing ISBNX has no meaning (or identity). Section 06

5. Implementing Entity Integrity in SQL • SQL provides direct support for Entity Integrity through CONSTRAINTS • Always set any attribute which is part of a primary key to NOT NULL. CREATE TABLE BOOK ( ISBN CHAR(8) NOT NULL , PUB_CODE CHAR(4) , TITLE CHAR(40) , PUB_DATE CHAR(4) , NOW_PRICE NUMBER(10,2), CONSTRAINT book_primary_key PRIMARY KEY (ISBN) ) ; Section 06

6. Referential Integrity • Referential Integrity is to do with the use of Foreign Keys and how we can be sure that only valid values of foreign key values are stored in a database. Example: • It would seem reasonable to assume that for each EMPLOYEE# in the TASK table there is an appropriate row in the EMPLOYEE table. • Does the example live up to this? • Not a good idea at all. Section 06

7. Implementing Referential Integrity (SQL-92) CREATE TABLE PUBLISHER ( PUB_CODE CHAR(4) NOT NULL, PUB_NAME CHAR(20)NOT NULL, CONSTRAINT pub_primary_key PRIMARY KEY (PUB_CODE)); CREATE TABLE BOOK ( ISBN CHAR(8) NOT NULL , PUB_CODE CHAR(4) , TITLE CHAR(40) , PUB_DATE CHAR(4) , NOW_PRICE NUMBER(10,2), CONSTRAINT book_foreign_key FOREIGN KEY (PUB_CODE) REFERENCES PUBLISHER (PUB_CODE), CONSTRAINT book_primary_key PRIMARY KEY (ISBN) ) ; • This shows a basic implementation of referential integrity. • There is more … • What happen if we delete a BOOK? Section 06

8. Why do we need to do more than just specify the foreign Key? • We need to tell the RDBMS what to do in the following situations. • When a row containing an invalid foreign-key value is inserted in a table. • When a foreign key in a table is updated with an invalid value. • When a primary key that is referenced as a foreign key in another table is updated. • When a row with a primary key that is referenced as a foreign key in another table is deleted. • We will deal with deletions first. Section 06

9. Referential Integrity and ON DELETE CASCADE CREATE TABLE BOOK ( ISBN CHAR(8) NOT NULL , PUB_CODE CHAR(4) , TITLE CHAR(40) , PUB_DATE CHAR(4) , NOW_PRICE NUMBER(10,2), CONSTRAINT book_foreign_key FOREIGN KEY (PUB_CODE) REFERENCES PUBLISHER (PUB_CODE) ON DELETE CASCADE, CONSTRAINT book_primary_key PRIMARY KEY (ISBN) ) ; • Some new syntax. • If a PUBLISHER is deleted (in the publisher table) then all matching records are deleted in the BOOK table. • It ‘cascades’ through the book table. • Is this the right strategy when a PUBLISHER is deleted? Section 06

10. Referential Integrity and ON DELETE SET DEFAULT CREATE TABLE BOOK ( ISBN CHAR(8) NOT NULL , PUB_CODE CHAR(4) DEFAULT ‘CORG’, TITLE CHAR(40) , PUB_DATE CHAR(4) , NOW_PRICE NUMBER(10,2), CONSTRAINT book_foreign_key FOREIGN KEY (PUB_CODE) REFERENCES PUBLISHER (PUB_CODE) ON DELETE SET DEFAULT, CONSTRAINT book_primary_key PRIMARY KEY (ISBN) ) ; • If a PUBLISHER is deleted (in the publisher table) then all matching records in the BOOK table have their PUB_CODE set to the value ‘CORG’. • This is the DEFAULT value for PUB_CODE • This is just an example of the syntax - using ‘CORG’ as the DEFAULT may not actually be a good idea. Section 06

11. Referential Integrity and ON DELETE SET NULL CREATE TABLE BOOK ( ISBN CHAR(8) NOT NULL , PUB_CODE CHAR(4) , TITLE CHAR(40) , PUB_DATE CHAR(4) , NOW_PRICE NUMBER(10,2), CONSTRAINT book_foreign_key FOREIGN KEY (PUB_CODE) REFERENCES PUBLISHER (PUB_CODE) ON DELETE SET NULL, CONSTRAINT book_primary_key PRIMARY KEY (ISBN) ) ; • If a PUBLISHER is deleted (in the publisher table) then all matching records in the BOOK table have their PUB_CODE set to NULL. • Is this a better idea? • Perhaps we don’t want to lose the BOOK records although the PUBLISHER has been deleted. Section 06

12. Referential Integrity and ON DELETE NO ACTION CREATE TABLE BOOK ( ISBN CHAR(8) NOT NULL , PUB_CODE CHAR(4) , TITLE CHAR(40) , PUB_DATE CHAR(4) , NOW_PRICE NUMBER(10,2), CONSTRAINT book_foreign_key FOREIGN KEY (PUB_CODE) REFERENCES PUBLISHER (PUB_CODE) ON DELETE NO ACTION, CONSTRAINT book_primary_key PRIMARY KEY (ISBN) ) ; • If a user attempts to delete a PUBLISHER (in the publisher table), and there are matching records in the BOOK table, then the delete is refused. • You have to remove all references to a particular PUB_CODE in the BOOK table before you can delete that PUBLISHER. Section 06

13. Referential Integrity and ON UPDATE CASCADE CREATE TABLE BOOK ( ISBN CHAR(8) NOT NULL , PUB_CODE CHAR(4) , TITLE CHAR(40) , PUB_DATE CHAR(4) , NOW_PRICE NUMBER(10,2), CONSTRAINT book_foreign_key FOREIGN KEY (PUB_CODE) REFERENCES PUBLISHER (PUB_CODE) ON UPDATE CASCADE, CONSTRAINT book_primary_key PRIMARY KEY (ISBN) ) ; • If a PUB_CODE value is updated (changed) in the publisher table then all matching records are updated in the BOOK table. • It ‘cascades’ through the book table. Section 06

14. Referential Integrity and ON UPDATE SET DEFAULT CREATE TABLE BOOK ( ISBN CHAR(8) NOT NULL , PUB_CODE CHAR(4) DEFAULT ‘CORG’, TITLE CHAR(40) , PUB_DATE CHAR(4) , NOW_PRICE NUMBER(10,2), CONSTRAINT book_foreign_key FOREIGN KEY (PUB_CODE) REFERENCES PUBLISHER (PUB_CODE) ON UPDATE SET DEFAULT, CONSTRAINT book_primary_key PRIMARY KEY (ISBN) ) ; • If a PUB_CODE value is updated (changed) in the publisher table then all matching records in the BOOK table are set to the DEFAULT value. • Clearly not what you would do with PUBLISHER and BOOK. Section 06

15. Referential Integrity and ON UPDATE SET NULL CREATE TABLE BOOK ( ISBN CHAR(8) NOT NULL , PUB_CODE CHAR(4) , TITLE CHAR(40) , PUB_DATE CHAR(4) , NOW_PRICE NUMBER(10,2), CONSTRAINT book_foreign_key FOREIGN KEY (PUB_CODE) REFERENCES PUBLISHER (PUB_CODE) ON UPDATE SET NULL, CONSTRAINT book_primary_key PRIMARY KEY (ISBN) ) ; • If a PUB_CODE value is updated (changed) in the publisher table then all matching records in the BOOK table are set to NULL. • Again probably a bad idea for this example but useful in other situations. Section 06

16. Referential Integrity and ON UPDATE NO ACTION CREATE TABLE BOOK ( ISBN CHAR(8) NOT NULL , PUB_CODE CHAR(4) , TITLE CHAR(40) , PUB_DATE CHAR(4) , NOW_PRICE NUMBER(10,2), CONSTRAINT book_foreign_key FOREIGN KEY (PUB_CODE) REFERENCES PUBLISHER (PUB_CODE) ON UPDATE NO ACTION, CONSTRAINT book_primary_key PRIMARY KEY (ISBN) ) ; • If a user attempts to change a PUB_CODE value in the publisher table, and there are matching records in the BOOK table, then the delete is refused. Section 06

17. Referential Integrity continued ... • Finally: • Most normal updates will not cause such problems. • Inserting a new BOOK row with a valid PUB_CODE will have no effect on Referential Integrity. • Deleting PUBLISHERS or updating PUB_CODEs in the PUBLISHER table are quite rare events. Section 06

18. Referential Integrity – MS SQL Server 2000 Enterprise Manager Section 06

19. END OF LECTURE Section 06