DBMS : Integrity Constrain

1. Chapter 5 DBMS : Integrity Constrain SUCHADA PUNNOI Dept. of Computer Science & Information Technology http://computer.pcru.ac.th/suchada/

2. Integrity Constrainกฎความคงสภาพของข้อมูล

3. Integrity constraint • Integrity constraints กฎความคงสภาพถูกกำหนดให้แก่ฐานข้อมูล โดยมีจุดมุ่งหมายเพื่อให้ข้อมูลที่ปรากฏในฐานข้อมูล ณ. ขณะใดขณะหนึ่งมีความถูกต้อง (valid database state)

4. ชนิดของความคงสภาพ(Integrity Type) • Integrity type สำหรับโมเดลเชิงสัมพันธ์ประกอบด้วย • กฎความคงสภาพของเอนติตี (Entity integrity) • กฎความคงสภาพในการอ้างอิงถึง (Referential integrity) • กฎความสะภาพของโดเมน (Domain integrity)

5. Domain Constraint • เป็นการกำหนดกฎเกณฑ์ของค่าที่จะปรากฏในแต่ละแอตตริบิวใน รีเลชัน (คุณสมบัติประการหนึ่งคือแต่ละคอลัมน์ต้องมีค่าเพียงค่าเดียว) • ชนิดของข้อมูล (Data Type) • รูปแบบของข้อมูล (FORMATE) • ขอบเขตค่าของข้อมูล (RANGE) • ต้องมีค่าหรือไม่ (NULL or NOT NULL) • มีค่าซ้ำกันได้หรือไม่ (UNIQUE OR NOT UNIQUE)

6. Entity integrity, Referential Integrity and foreign key • Entity integrity constraint สถานะที่ค่าของคีย์หลักหรือprimary keyจะต้องไม่มีค่าเป็นnull. • Why? • เพราะprimary keyถูกใช้ในการอ้างอิงถึงแต่ละ tuplesใน relationดังนั้นถ้ามีค่าเป็น null ก็จะไม่สามารถใช้ในการอ้างถึงแต่ละ tuples ได้ • หากมีหลาย tuples ที่มีค่าเป็น null, เราจะไม่สามารถแยกความแตกต่างระหว่างแต่ละtupleได้ และทำให้ไม่สามารถที่จะอ้างอิงจากตารางอื่นได้อีกด้วย

7. กฎความคงสภาพในการอ้างอิงถึง (Referential Integrity Constraint) • เชื่อมระหว่าง 2 relations • ถูกใช้เพื่อดูแล หรือmaintain ความถูกต้องและสอดคล้องกัน (consistency) ของข้อมูลระหว่าง tuples ของ 2 relations • referential integrity constraint อยู่ในสภาวะที่ tuple ใน relation หนึ่งที่มีการอ้างถึง(refer to) tuple ในอีกrelation จะต้องอ้างถึงtuple ที่มีอยู่จริงใน relation เท่านั้น

8. คุณสมบัติของคีย์ (Key Property) • คีย์(key)ต้องมีคุณสมบัติสอดคล้องกับ2 เงื่อนไขต่อไปนี้ • ใน relation เดียวกันต้องไม่มี 2 tuples ที่มีค่าในทกๆ attributes ที่เป็นส่วนประกอบของ key เหมือนกัน (ซ้ำกัน)(Uniqueness) • แอตตริบิวที่ประกอบเป็นคีย์ จะ ต้องเป็น minimum set เท่านั้นที่ทำให้มีคุณสมบัติในข้อ 1 (uniqueness) • ต้องไม่สามารถที่จะทำการremove attributes ใดออกไปได้แล้วยังทำให้คงมีคุณสมบัติuniqueness constraint ในเงื่อนไขที่ 1 (Minimality)

9. Candidate และ Primary key • ใน relation อาจมีคีย์มากกว่าหนึ่ง key. • แต่ละ Key ถูกเรียกว่าCandidate Key • ตัวอย่างเช่น Relation Student Student (ID,FNAME,LNAME, TCODE, FACTCODE, DEPTCODE) • มี 2 candidate keys: ID และ FNAME+LNAME • มี candidate key เพียงตัวเดียวถูกเลือกให้เป็นPrimary Key ของ relation

10. Foreign key • เซตของ attributes FK ใน relation R1 เป็น foreign key ของ R1 ที่มีการ references ไปยัง relation R2 ถ้าสอดคล้องกับ 2rules ต่อไปนี้คือ • attributes ใน FK มี domain(s)เหมือนโดเมนของ primary key attributes (PK) of R2; attributes FK มีการอ้างอิง(reference หรือ refer to) ไปยัง relation R2 • ค่าของ FK ใน tuple t1ของ r1(R) ต้องมีการสอดคล้องกับเงื่อนไขใดเงื่อนไขหนึ่งต่อไปนี้คือ • ค่าของ PK สำหรับบาง tuple t2 ของ the current state r2(R) • หรือมีค่าเป็น null. • t1[FK] = t2[PK], กล่าวได้ว่าtuple t1 references หรือ refers to ไปยัง tuple t2

11. Specified constraints • จะต้องให้ความหมายและบทบาทของแต่ละ attributes ที่ชัดเจน • Referential integrity constraint บอกความสัมพันธ์ระหว่างเอนติตี

12. กำหนด Basic Constraints ใน SQL • Entity Integrity • Create table Table_name (Col_name1 data_type constraint , col_name2data_type constraint, …, col_namen Data_type constraint, Constraint Cont_name Primary Key (key attribute)

13. Example GStudent (ID,NAME) SQL> create table GStudent (id varchar2(10), name varchar2(30) not null, constraint gStuPk primary key (id));

14. SQL> connect system/dba_passwd@tori Connected. SQL> desc dba_constraints Name Null? Type ----------------------------------------- -------- ---------------- OWNER NOT NULL VARCHAR2(30) CONSTRAINT_NAME NOT NULL VARCHAR2(30) CONSTRAINT_TYPE VARCHAR2(1) TABLE_NAME NOT NULL VARCHAR2(30) SEARCH_CONDITION LONG R_OWNER VARCHAR2(30) R_CONSTRAINT_NAME VARCHAR2(30) DELETE_RULE VARCHAR2(9) STATUS VARCHAR2(8) DEFERRABLE VARCHAR2(14) DEFERRED VARCHAR2(9) VALIDATED VARCHAR2(13) GENERATED VARCHAR2(14) BAD VARCHAR2(3) RELY VARCHAR2(4) LAST_CHANGE DATE SQL> select constraint_name, constraint_type from dba_constraints where owner = 'A' CONSTRAINT_NAME C -------------------------------------------- GSTUPK P SYS_C001127 C Oracle example

15. Example DEPT (FCODE,DCODE,DNAME) SQL> create table DEPT (FCODE char (2), DCODE char (3), Name varchar2(30) not null, constraint DeptPk primary key (Fcode,Dcode));

16. Specifying key and referential integrity constraint • Primary • CONSTRINT Constraint_Name Primary Key (Key Attribute) • Referential Integrity • CONSTRAINT Constraint_Name Foreign Key (FK attri) references Table_name (Key_attri) On BusinessRule

17. Example create tableTeacher (tcode varchar2(2), tname varchar2(30) not null, constraint TeacherPK Primary key (tcode)) alter table gstudent add (tcode varchar2(2)); alter tablegStudent add (constraint GSTUFK foreign key (tcode) references teacher(tcode))

18. WHY INTEGRITY is Importance?

19. Foreign keyมีค่าเป็นNULL? • คำตอบคือค่าของฟอร์เรนท์คีย์ จะเป็น NULLหรือไม่ขึ้นกับ กฎเกณฑ์ของระบบ (Business Rule) ที่เรากำลังทำการศึกษา

20. การเพิ่มข้อมูล (Insert) • แบบขึ้นต่อกัน (DEPENDENT) • แบบอัตโนมัติ (AUTOMATIC) • แบบกำหนดค่าเป็นนัล (NULLIFY) • แบบกำหนดค่าให้ (DEFAULT)

21. แบบขึ้นต่อกัน • อนุญาตให้ทำการเพิ่มข้อมูลใน Relationลูกได้ ก็ต่อเมื่อข้อมูลใน Relationลูกมีค่าสอดคล้องกับค่าข้อมูลใน Relationแม่เท่านั้น • (FK ต้องมีค่าสอดคล้องกับ PK ในรีเลชันที่มีการอ้างอิงถึง)

22. แบบอัตโนมัติ • อนุญาตให้เพิ่มค่าใน Relation ลูกตลอดเวลา • แต่ถ้าใน Relationแม่ไม่มีข้อมูลที่สอดคล้อง ต้องทำการเพิ่มค่าใน Relation แม่ก่อน

23. แบบกำหนดค่าเป็นนัล (NULLIFY) • อนุญาตให้เพิ่มค่าใน Relation ลูกตลอดเวลา • แต่ถ้าใน Relationแม่ไม่มีข้อมูลที่สอดคล้อง ให้ทำการกำหนดค่าใน Relationแม่ เป็นนัล (null)

24. แบบกำหนดค่าให้ (DEFAULT) • อนุญาตให้เพิ่มค่าใน Relation ลูกตลอดเวลา • แต่ถ้าใน Relationแม่ไม่มีข้อมูลที่สอดคล้อง ให้ทำการกำหนดค่าใน Relationแม่ เป็นค่าเฉพาะ

25. Insert : 006 Somsak ??

26. Delete (Refer to others) • แบบมีเงื่อนไข (Restrict) • แบบกระทำเป็นทอดๆ (Cascade) • แบบกำหนดค่าเป็นนัล (NULLIFY) • แบบกำหนดค่าให้ (DEFAULT)

27. แบบมีเงื่อนไข (Restrict) • อนุญาตให้ลบค่าใน Relation แม่ได้ต้องไม่มีข้อมูลในตารางลูกที่มีการเชื่อมมายังตารางแม่ • แต่ถ้าใน Relationแม่มีข้อมูลที่สอดคล้องลบไม่ได้

28. แบบกระทำเป็นทอดๆ (Cascade) • อนุญาตให้ลบค่าใน Relation แม่ได้หากไม่มีการอ้างอิงถึง • แต่ถ้าใน Relationแม่มีการอ้างอิงถึง • จะทำการลบข้อมูลในตารางแม่ • แล้วตามไปลบข้อมูลในตารางลูกทุกตัวที่มีการอ้างอิงถึง

29. แบบกำหนดค่าเป็นนัล (NULLIFY) • อนุญาตให้ลบค่าใน Relation แม่หากไม่มีการอ้างอิงถึง • แต่ถ้าใน Relationแม่มีการอ้างอิงถึง • เมื่อการลบข้อมูลในตารางแม่ • ตารางลูกที่มีการอ้างอิงมา ต้องถูกกำหนดค่าเป็น นัล (null)

30. แบบกำหนดค่าให้ (DEFAULT) • อนุญาตให้ลบค่าในRelation แม่หากไม่มีการอ้างอิงถึง • แต่ถ้าใน Relationแม่มีการอ้างอิงถึง • เมื่อการลบข้อมูลในตารางแม่ • ตารางลูกที่มีการอ้างอิงมา ต้องถูกกำหนดค่าเป็นค่าที่กำหนดไว้

31. Delete : T4 Pongrat

32. Example SQL create table dept ( fcode varchar2(2), dcode varchar2(2), dname varchar2(30), primary key(fcode,dcode), foreign key (fcode) references fact(fcode) on delete cascade)

33. Change the Values of PK • เปลี่ยนแบบมีข้อจำกัด (Restricted) • ทำการเปลี่ยนแปลงได้ก็ต่อเมื่อไม่มีการอ้างอิงจาก FK ในตารางอื่น • เปลี่ยนแบบเป็นทอดๆ (Cascade) • ทำการเปลี่ยนได้โดยต้องตามไปเปลี่ยนทุกที่ที่มีการอ้างอิงถึง • เปลี่ยนแบบกำหนดค่าเป็นนัล(Nullify) • ทำการเปลี่ยนแปลงได้โดยต้องทำการเปลี่ยนค่าทุกที่ที่มีการอ้างอิงถึงให้เป็นนัล

34. UPDATE OPERATIONS and DEALING with Constraint Violation • INSERT OPERATION • Provide of a list of a new Tuple t that insert in Relation R • Domain Constraint can be violated if an attribute value is given that does not appear in the corresponding domain. • Key constraint can be violated if a key value in the new tuple t already exist in another tuple in the relation r(R) • Entity integrity can be violated if the primary key of the new tuple t is null. • Referential Integrity can be violated if the value of any foreign key in t refer to a tuple that does not exist in the reference relation.

35. DELETE OPERATION • การลบข้อมูลอาจไม่สามารถลบออกได้หากว่าในการลบนั้น รายการที่ต้องการลบออกไปมีการอ้างอิงถึงจากตารางอื่น (นั่นคือ ฟอร์เรนท์คีย์ มีการอ้างอิงถึง)

36. The Update Operation • It is used to change the values if one or more attributes in a tuple (or tuples) of some relation R. • It is necessary to specify a condition on the attributes of the relation to select the tuple to be modified.

37. กฏความคงสภาพของโดเมน • เป็นการกำหนดเงื่อนไขข้อจำกัดสำหรับแอตตริบิวต่างๆ • ชนิดของข้อมูล • รูปแบบ เช่น วันที่ DD-MM-YY • Range ex. GPA = 0 – 4 • ค่าซ้ำกันได้หรือไม่ • มีค่าเป็นนัลได้หรือไม่ • ผู้บริหารฐานข้อมูลต้องทำการกำหนดกฎเกณฑ์ต่างๆ ไว้เพื่อควบคุมความถูกต้องของข้อมูล

38. ต้องเป็นตัวใหญ่เท่านั้นต้องเป็นตัวใหญ่เท่านั้น ไม่ใส่ค่าจะกำหนดให้เป็นวันที่ของเครื่อง เงินเดือนต้องเกิน 500 Example Domain integrity Create table teacher tcode NUMBER Constraint pk_tcode Primary key, tname varchar2(10) Constraint nn_ename not null Constraint upper_ename CHECK(tname = UPPER(tname)), job varchar2(9), startDate Date DEFAULT SYSDATE, sal number(10,2) Constraint cv_sal CHECK (sal > 500), ……

39. SQL>INSERT INTO TEACHER (TCODE,TNAME) VALUES ('01','sompong'); * ERROR at line 1: ORA-02290: check constraint (NOK.UPPER_ENAME) violated • SQL> INSERT INTO TEACHER (TCODE,TNAME) VALUES ('01','SOMPONG') • 1 row created. • SQL> select * from teacher; • TCODE TNAME STARTDATE SAL • ---------- ---------- ------------- ---------- • 1 SOMPONG 24 ม.ค. 2005

40. F A Q Thank You !