BAGIAN 2 - INTEGRITAS DATA 1. Introduction to Data Integrity Kolom-kolom dalam table memiliki rules tertentu yang membatasi (meng-constrain) data yang ada di dalamnya. 1.1. Tipe-tipe Rules yang menjamin tipe Integritas Data di dalamnya adalah: a. NULL Rule A null is a rule defined on a single column that allows or disallows inserts or updates of rows containing a null (the absence of a value) in that column. b. UNIQUE Column Value A unique value defined on a column (or set of columns) allows the insert or update of a row only if it contains a unique value in that column (or set of columns). c. PRIMARY Key Values A primary key value defined on a key (a column or set of columns) specifies that each row in the table can be uniquely identified by the values in the key. d. Referential Integrity A rule defined on a key (a column or set of columns) in one table that guarantees that the values in that key match the values in a key in a related table (the referenced value).
| RESTRICT |
Rules yang terkait dengan manipulasi data-nya adalah : i. RESTRICT : melarang update dan delete data rujukan ii. NO ACTION : melarang update dan delete data rujukan. Bedanya dengan RESTRICT adalah dilaksanakan pada akhir statement. iii. SET TO NULL : semua dependent data di set ke NULL jika data rujukan di update atau di-delete iv. SET TO DEFAULT : semua dependent data di set ke nilai DEFAUKT jika data rujukan di update atau di-delete v. CASCADE : Jika data rujukan di update atau di delete, maka semua data yang tergantung atau terhubung pada data rujukan ikut di-update. e. Complex Integrity Checking Complex integrity checking is a user-defined rule for a column (or set of columns) that allows or disallows inserts, updates, or deletes of a row based on the value it contains for the column (or set of columns). 1.2. Penerapan (Enforces/pemaksaan) Rules a. Declarative / Using INTEGRITY CONSTRAINT 1.2.a.1. NOT NULL yang berhubungan dengan NULL value pada kolom 1.2.a.2. UNIQUE KEY yang berhubungan dengan UNIQUE Column Value 1.2.a.3. PRIMARY KEY yang berhubungan dengan PRIMARY Identification Values 1.2.a.4. FOREIGN KEY yang berhubungan dengan Referential Integrity, dengan ACTION yang terkait seperti telah diterangkan sebelumnya adalah :
a. Update and delete No Action, menolak operasi penghapusan dan update dari parent table. Merupakan default jika aturan ON DELETE dihilangkan b. Delete CASCADE Menghapus baris dari parent table dan secara otomatis menghapus baris yang sesuai dalam child table, jika baris yang dihapus tadi merupakan candidate key yang digunakan sebagai foreign key pada tabel lainnya, maka aturan foreign key untuk tabel ini dihilangkan. c. Delete SET NULL menghapus baris pada parent table dan menetapkan nilai foreign key dalam child table menjadi NULL. Berlaku jika kolom foreign key mempunyai qualifier NOT NULL. d. SET DEFAULT, menghapus baris dari parent table dan menetapkan setiap komponen foreign key dari child table menjadi default yang telah ditetapkan. Berlaku jika kolom foreign key memliki nilai DEFAULT. 1.2.a.5. CHECK yang berhubungan dengan Rules Complex Integrity b. Non Declarative / using DATABASE TRIGGER 2. Sekilas Tentang Integrity Constraint 2.1. Keuntungan Integrity Cosntraint ( yang berikut ini diartikan sendiri ) • Enforcing business rules in the code of a database application • Using stored procedures to completely control access to data • Enforcing business rules with triggered stored database procedures 2.1.1. Declarative Ease Define integrity constraints using SQL statements. When you define or alter a table, no additional programming is required. The SQL statements are easy to write and eliminate programming errors. Oracle controls their functionality. For these reasons, declarative integrity constraints are preferable to application code and database triggers. The declarative approach is also better than using stored procedures, because the stored procedure solution to data integrity controls data access, but integrity constraints do not eliminate the flexibility of ad hoc data access. 2.1.2. Centralized Rules Integrity constraints are defined for tables (not an application) and are stored in the data dictionary. Any data entered by any application must adhere to the same integrity constraints associated with the table. By moving business rules from application code to centralized integrity constraints, the tables of a database are guaranteed to contain valid data, no matter which database application manipulates the information. Stored procedures cannot provide the same advantage of centralized rules stored with a table. Database triggers can provide this benefit, but the complexity of implementation is far greater than the declarative approach used for integrity constraints. 2.1.3. Maximum Application Development Productivity If a business rule enforced by an integrity constraint changes, then the administrator need only change that integrity constraint and all applications automatically adhere to the modified constraint. In contrast, if the business rule were enforced by the code of each database application, developers would have to modify all application source code and recompile, debug, and test the modified applications. 2.1.4. Immediate User Feedback Oracle stores specific information about each integrity constraint in the data dictionary. You can design database applications to use this information to provide immediate user feedback about integrity constraint violations, even before Oracle runs and checks the SQL statement. For example, an Oracle Forms application can use integrity constraint definitions stored in the data dictionary to check for violations as values are entered into the fields of a form, even before the application issues a statement. 2.1.5. Superior Performance The semantics of integrity constraint declarations are clearly defined, and performance optimizations are implemented for each specific declarative rule. The Oracle optimizer can use declarations to learn more about data to improve overall query performance. (Also, taking integrity rules out of application code and database triggers guarantees that checks are only made when necessary.) 2.1.6. Flexibility for Data Loads and Identification of Integrity Violations
You can disable integrity constraints temporarily so that large amounts of data can be loaded without the overhead of constraint checking. When the data load is complete, you can easily enable the integrity constraints, and you can automatically report any new rows that violate integrity constraints to a separate exceptions table.
3.
TIPE INTEGRITY CONSTRAINTS : 3.1. NOT NULL Integrity Constraints Null means the absence of a value Suatu kolom yang didefinisikan dengan constraint NOT NULL tidak boleh berisi nilai NULL. Kolom yang befungsi sebagai kunci primer (primary key) otomatis tidak boleh NULL. 3.1.1. Contoh
Baris terakhir gagal diinsert. Karena sudah didefinisikan sebagai NOT NULL. Jika ingin menyisipkan, maka kolom-a harus di NULL-kan dahulu.
3.1.2.
Lalu anda coba lagi baris ketiga, pasti berhasil. Contoh
3.1.3.
Contoh yang ada di schema HR
3.2.
UNIQUE Key Integrity Constraints Constraint UNIQUE mendefinisikan suatu kolom menjadi bersifat unik. Suatu kolom : jika hanya 1 (satu kolom) disebut kolom UNIQUE key
Suatu kolom : jika terdiri beberapa kolom disebut COMPOSITE UNIQUE KEY CONSTRAINT. Kolom yang dapat digabungkan (maksimum) adalah 32 kolom.
3.2.1.
Cara Penulisan Sintaknya :
3.2.2.
Contoh 1 Kolom Tunggal:
Lalu sisipkan
Lalu coba lagi insertkan UK (2) dari baris kedua Drop UK dulu, baru ditambahkan seperti berikut :
3.2.3.
Contoh 2 Kolom Jamak
3.2.4.
Memberi nama kolom
3.3.
PRIMARY KEY Integrity Constraints Constraint PRIMARY KEY membentuk key yang unik untuk suatu table. Kolom yang didefinisikan sebagai PRIMARY KEY akan mengidentifikasi suatu baris data menjadi unik. Aturannya : • Tidak boleh ada duplikasi baris dalam suatu kolom/kumpulan kolom pada suatu table. • Tidak boleh NULL pada kolom PK. Aturan COMPOSITE Primary sama seperti unique key ( 32 kolom ) 3.3.2.
Contoh
3.3.3.
Contoh
3.3.4.
Penulisan Sintak
3.3.5.
Contoh 1
Memberi nama
3.4.
Referential Integrity Constraints Integritas referensial adalah seperangkat aturan yang mengatur hubungan antara kunci primer dengan kunci tamu milik tabel-tabel yang berada dalam suatu basis data relasional untuk menjaga konsistensi data. Tujuan integritas referensial sendiri adalah untuk menjamin dan memastikan agar entitas dalam suatu tabel yang menunjuk ke suatu pengenal unik pada suatu baris di tabel lain benar-benar menunjuk pada nilai yang memang ada. Harus ada rules yang mengatur penggunaan kolom bersama tersebut. Istilah yang timbul karena hubungan tersebutg di antaranya adalah : • Foreign key : Suatu kolom yang ada pada suatu table, dimana kolom tersebut juga dimiliki oleh table yang lain sebagai suatu PRIMARY KEY. Mendefinisikan kolom yang ada pada tabel anak • Referenced key : Mengidentifikasi tabel dan kolom dalam tabel induk. Unique key or primary key yang dirujuk oleh foreign key. Bisa pada table yang sama, biaspula pada table yang berbeda. • Dependent or child table : Table yang di dalamnya terdapat foreign key, dan tergantung (dependent) pada nilai yang ada di unique atau primary key.
•
3.4.2.
Referenced or parent table : Table yang merupakan rujukan dari table anak. Tabel inilah yang menentukan apakan insert atau update diperbolehkan di table anak. Contoh Foreign Key pada table yang berbeda ( induk dan anak )
3.4.3.
Contoh Foreign Key pada table yang sama / Self-Referential Integrity Constraints
3.4.4.
Contoh Foreign ley pada table yang berbeda
3.4.5.
Penulisan Syntax
3.4.6.
Actions Defined by Referential Integrity Constraints. ACTION yang terkait Aksi yang terjadi di baris pada tabel anak jika terjadi modifikasi nilai pada tabel induk adalaah UPDATE dan DELETE NO ACTION, and DELETE CASCADE.
[ON DELETE {CASCADE | NO ACTION} ] Pilihan ini akan menentukan aksi apa yang akan di lakukan terhadap sebuah baris/ record dalam tabel, jika record tersebut memiliki hubungan referensi (referential relationship) dan record yang di referensi di hapus (DELETE) dari tabel induk. Defaultnya adalah NO ACTION. • Jika pilihan CASCADE di gunakan, sebuah record akan di hapus dari table referensinya jika record tersebut di hapus dari tabel induknya • Jika pilihan NO ACTION yang digunakan, SQL Server akan menampilkan pesan kesalahan dan proses penghapusan record pada tabel induk di batalkan (ROLLED BACK). Dengan kata lain, jika pilihan NO ACTION yang digunakan, maka akan menampilkan pesan kesalahan dan membatalkan penghapusan data untuk record di suatu tabel dosen jika terdapat paling tidak satu record di tabel lain yang berhubungan dengan record tersebut. [ON UPDATE {CASCADE | NO ACTION} ] Sama dengan [ON DELETE {CASCADE | NO ACTION} ], yang membedakan jika ON DELETE aksi dilakukan jika record di hapus (DELETE), pada ON UPDATE aksi dilakukan jika record di rubah (UPDATE).
3.4.7. •
•
Catatan Delete No Action NO ACTION (DEFAULT) : memerintahkan agar Key Values Rujukan tidak boleh di-DELETE atau di-UPDATE, jika hasilnya akan mem-violate sebuah referential integrity constraint. Contoh : Jika PK dijadikan rujukan oleh FK, maka PK tersebut tidak dapat didelete karena adanya ketergantungan data. Delete Cascade Memerintahkan agar semua baris di table anak dengan key value FK juga di delete karena Key Values rujukan di-delete.
•
Contoh : Jika sebuah baris yang mengandung PK di table induk dihapus, dimana PK tersebut dijadikan rujukan oleh beberapa FK di table anak, maka semua baris di table anak juga ikut dihapus. Delete Set Null Memerintahkan semua baris di table anak berisi value NULL saat baris Key Value rujukan dihapus. Contoh : Empployee_ID merujuk Manager_ID di Tabel TMP, maka jika manager dihapus akan menyebabkan semua pekerja yangmempunyai manager_id akan berisi NULL.
3.5.
CHECK Integrity Constraints Constraint CHECK digunakan untuk mendefinisikan suatu kondisi yang harus dipenuhi oleh tiap baris data dalam table. 3.5.1. Syarat : • Harus berupa Boolean expression menggunakan nilai-nilai di baris yang akan di insert atau update. • Tidak boleh berisi - SUBQUERIES, - SEQUENCE, - FUNGSI SQL SYSDATE, UID, USER atau USERENV, - atau PseudoCOlumn LEVEL atau ROWNUM. 3.5.2. Multiple CHECK : Satu kolom dapat memliki multiple CHECK Constraint dan tidak ada batasnya. 3.5.3. Contoh 1 menentukan agar gaji selalu diinput lebih dari 0:
3.5.4.
Contoh 2 Menentukan range gaji di antara 0 dan ….
3.5.5.
Contoh 3 Menambah cek setelah table dibuat
3.5.6.
Contoh 4 Menentukan gaji pemula pasti lebih kecil dari pengakhir
4.
MEKANISME PENGECEKAN CONSTRAINTS 4.1. Kenapa perlu ? Contoh pemahaman : SISIPKAN satu baris ke baris-1 tabel emp, dimana value PK MGR = 7331 belum ada ?
4.2. Tiga kejadian yang mungkin terjadi , menggunakan scenario DEFER, yaitu Sisipkan semua baris, baru di cek violation-nya. Hal ini dikarenakan ORACLE menjalankan perintah terlebih dahulu, dan terakhirnya adalah statementnya. 4.2.1. Insert-kan NULL ke kolom MANAGER ( anggap constraint NOT NULL tidak ada), pasti sukses. 4.2.2. Insert-kan Nilai yang sama ke empno dan manager. 4.2.3. Insert-kan nolai dengan Multiple Row, misalnya INSERT dengan nested-nya SELECT. Misalnya, baris ke satu empno = 200 dan mgr = 300 sedangkan baris keduanya empno=300 dan mgr=200. 5.
Deferred Constraint Checking (penundaan pengecekan constraint) DEFERED , ditunda jika system mengecek, bahwa statement hanya dapat dipenuhi pada saat statement COMMIT. Jika terdapat violation, maka menyebabkan transaksi undo/tidak jadi. IMMEDIATE/NOT DEFERRED, maka pengecekan terjadi segera di akhir statement. Jika terdapat violation, maka terjadi roll-back statement. Jika suatu constraint memerintahkan aksi ( misalnya delete cascade), maka aksi tersebut dijadikan bagian dari statement yang menyebabkannya, DEFERRED atau IMMEDIATE. 5.1. Atribut constraint 5.1.1. DEFERRABLE or NOT DEFERRABLE DEFERRABLE akan membuat suatu constraint tersebut dapat di-DEFERRED, yang mengoverload nilai default NOT DEFERRABLE dari setiap tabel yang dibuat. 5.1.2. INITIALLY DEFERRED or INITIALLY IMMEDIATE INTIALLY DEFERRED akan menunda evaluasi nilai dalam rangka referential integrity (implementasi constraint) hingga perintah COMMIT dijalankan. INITIALLY IMMEDIATE artinya sistem akan mengevaluasi nilai segera setelah nilai tersebut diinsert 5.1.3. INITIALLY IMMEDIATE DEFERRABLE, pada saat dibutuhkan kita bisa mengeset session kita menjadi DEFERRED untuk kemudian mengembalikannya kembali jadi IMMEDIATE saat mode DEFERRED sudah tidak diperlukan. 5.2. CONTOH :
5.2.1.
Menambah Constraint
5.2.2.
Menjadikan mode deferred
5.2.3.
Menjadi mode immediate
5.2.4.
Di defer saat dibutuhkan, lalu jika sudah tidak dibutuhkan kembali ke immediate
5.3. SET CONSTRAINTS IMMEDIATE dan juga SET CONSTRAINTS DEFERRED 5.3.1. SET CONSTRAINTS IMMEDIATE : dijalankan pada akhir statement, jika error, maka COMMIT tidak sukses 5.3.2. SET CONSTRAINTS DEFERRED 5.3.3. Perintahnya 5.3.4. ALTER SESSION SET CONSTRAINTS IMMEDIATE dan 5.3.5. ALTER SESSION SET CONSTRAINTS DEFERRED 6.
CONSTRAINTS STATE 6.1. Empat keadaan/states dan kombinasinya - ENABLE = mengijinkan incoming data yang sesuai dengan constraints - DISABLE = mengijinkan incoming data, tanpa melihat sesuai atau tidak sesuai constraints. - VALIDATE = meyakinkan data existing sesuai dengan constraints - NOVALIDATE = beberapa data existing mungkin tidak sesuai dengan constraints. Constraint State DISABLE
VALIDATE DISABLE VALIDATE
ENABLE
ENABLE VALIDATE (sm dgn ENABLE)
NOVALIDATE DISABLE NOVALIDATE (sm dgn DISABLE) ENABLE NOVALIDATE
Notes DISABLE VALIDATE = tidak mengijinkan perubahan pada kolom yang terkena constraints, men-disable constraint, men-Drop indeks constraints. ENABLE NOVALIDATE = constraints sudah dicheck, tetapi tidak semua baris TRUE. Digunakan untuk meyakinkan bahwa semua data baru atau data yang dimodikfikasi adalah VALID, sementara tidak yakin dengan data yang ada. 6.2. Transition States 6.2.1. ENABLE saja akan menyebabkan semua menjadi VALIDATE, kecuali ada disebut NOVALIDATE 6.2.2. DISABLE saja akan menyebabkan semua menjadi NOVALIDATE, kecuali ada disebutkan VALIDATE 6.2.3. NOVALIDATE dan VALIDATE tidak mempengaruhi disable dan enable 6.2.4. Jika PK atau UK : • DISABLE ENABLE, maka index tertentu akan di-create jika tidak ada index existing • ENABLE DISABLE, maka index akan didrop jika ada index existing 6.2.5. Jika Constraint dari • NOVALIDATE VALIDATE, semua data dicheck, sehingga akan lama. • VALIDATE NOVALIDATE, data tidak perlu di check 6.2.6. Single Constraint • ENABLE NOVALIDATE ENABLE VALIDATE state tidak mem-blok read, write, atau DDL statements. Lainnya.
