Oracle9i Introduction to SQL
@Tessy Badriyah, SKom. MT.
Oracle Academic Initiative Oracle9i Introduction to SQL
Oleh: Tessy Badriyah, SKom.MT
Politeknik Elektronika Negeri Surabaya Institut Teknologi Sepuluh Nopember Surabaya
BAB 11 : Constraint
Halaman : 11 - 0
Oracle9i Introduction to SQL
@Tessy Badriyah, SKom. MT. BAB 11 : Constraint
11.1. • • •
Sasaran Memahami definisi Constraint Dapat Membuat Constraint Dapat Melakukan pemeliharaan Constraint
11.2. Definisi Constraint Constraint adalah batasan atau aturan yang ada pada table. Constraint mencegah penghapusan data dari suatu table yang mempunyai keterkaitan dengan table yang lain. Misal terdapat keterkaitan antara table department dengan table pegawai. Dimana pada table pegawai menyimpan informasi kolom nomer department yang juga terdapat pada table department. Jika baris dengan nomer department ‘10’ akan dihapus dari table department, sedangkan terdapat data pegawai yang bekerja pada department tersebut, maka dengan adanya constraint antara dua table, penghapusan tersebut tidak bisa dilakukan. Oracle menyediakan beberapa tipe constraint berikut : • NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK Sintak penulisan Constraint : CREATE TABLE [schema.]table (column datatype [DEFAULT expr] [column_constraint], ... [table_constraint][,...]);
Contoh penulisan Constraint : CREATE TABLE employees( employee_id NUMBER(6), first_name VARCHAR2(20), ... job_id VARCHAR2(10) NOT NULL, CONSTRAINT emp_emp_id_pk PRIMARY KEY (EMPLOYEE_ID));
11.3. Constraint NOT NULL 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.
… NOT NULL constraint (tidak ada baris yang berisi nilai NULL untuk kolom last_name)
BAB 11 : Constraint
NOT NULL constraint
Tidak adanya NOT NULL constraint (Terdapat baris yang berisi nilai NULL untuk kolom department_id)
Halaman : 11 - 1
Oracle9i Introduction to SQL
@Tessy Badriyah, SKom. MT.
Contoh penggunaan Constraint NOT NULL : CREATE TABLE employees( employee_id NUMBER(6), last_name VARCHAR2(25) NOT NULL, salary NUMBER(8,2), commission_pct NUMBER(2,2), hire_date DATE CONSTRAINT emp_hire_date_nn NOT NULL, ...
diberi nama sistem
Diberi nama user
11.4. Constraint UNIQUE Constraint UNIQUE mendefinisikan suatu kolom menjadi bersifat unik. Ilustrasi penggunaan Constraint UNIQUE :
EMPLOYEES
… INSERT INTO
diperbolehkan Tidak diperbolehkan, karena sudah ada
Perintah pembuatan tabel untuk ilustrasi diatas : CREATE TABLE employees( employee_id NUMBER(6), last_name VARCHAR2(25) NOT NULL, email VARCHAR2(25), salary NUMBER(8,2), commission_pct NUMBER(2,2), hire_date DATE NOT NULL, ... CONSTRAINT emp_email_uk UNIQUE(email));
11.5. Constraint PRIMARY KEY Constraint PRIMARY KEY membentuk key yang unik untuk suatu table. Kolom yang didefinisikan sebagai PRIMARY KEY akan mengidentifikasi suatu baris data menjadi unik. Contoh penggunaan Constraint PRIMARY KEY : CREATE TABLE departments( department_id NUMBER(4), department_name VARCHAR2(30) CONSTRAINT dept_name_nn NOT NULL, manager_id NUMBER(6), location_id NUMBER(4), CONSTRAINT dept_id_pk PRIMARY KEY(department_id));
BAB 11 : Constraint
Halaman : 11 - 2
Oracle9i Introduction to SQL
@Tessy Badriyah, SKom. MT.
Ilustrasi penggunaan Constraint PRIMARY KEY :
DEPARTMENTS
PRIMARY KEY
… Tidak diperbolehkan (Null value)
INSERT INTO
Tidak diperbolehkan (nilai 50 sudah ada)
11.6. Constraint FOREIGN KEY FOREIGN KEY constraint didefinisikan pada suatu kolom yang ada pada suatu table, dimana kolom tersebut juga dimiliki oleh table yang lain sebagai suatu PRIMARY KEY. Ilustrasi penggunaan Constraint FOREIGN KEY : DEPARTMENTS
… EMPLOYEES FOREIGN KEY
…
INSERT INTO
Tidak diperbolehkan karena nilai 9 tidak ada DiperbolehDiperbolehkan
Perintah pembuatan tabel untuk ilustrasi diatas : CREATE TABLE employees( employee_id NUMBER(6), last_name VARCHAR2(25) NOT NULL, email VARCHAR2(25), salary NUMBER(8,2), commission_pct NUMBER(2,2), hire_date DATE NOT NULL, ... department_id NUMBER(4), CONSTRAINT emp_dept_fk FOREIGN KEY (department_id) REFERENCES departments(department_id), CONSTRAINT emp_email_uk UNIQUE(email));
BAB 11 : Constraint
Halaman : 11 - 3
Oracle9i Introduction to SQL
@Tessy Badriyah, SKom. MT.
11.7. Keyword dari Constraint FOREIGN KEY Berikut ini keyword yang ada pada Constraint FOREIGN KEY : • FOREIGN KEY : Mendefinisikan kolom yang ada pada tabel anak • REFERENCES : Mengidentifikasi tabel dan kolom dalam tabel induk • ON DELETE CASCADE : Penghapusan baris dependent dalam tabel anak pada saat sebuah baris pada tabel induk dihapus • ON DELETE SET NULL : Mengkonversi dependent foreign key ke nilai NULL. 11.8. Constraint CHECK Constraint CHECK digunakan untuk mendefinisikan suatu kondisi yang harus dipenuhi oleh tiap baris data dalam table. Contoh penggunaan : ..., salary NUMBER(2) CONSTRAINT emp_salary_min CHECK (salary > 0),... 11.9. Menambahkan suatu Constraint Untuk menambahkan suatu Constraint, digunakan perintah ALTER TABLE. Cara penulisan : ALTER TABLE table ADD [CONSTRAINT constraint] type (column);
11.10. Menambahkan Constraint Misal ditambahkan constraint FOREIGN KEY pada tabel EMPLOYEES yang menunjukkan bahwa data manager harus valid atau berasal dari tabel EMPLOYEES. ALTER TABLE employees ADD CONSTRAINT emp_manager_fk FOREIGN KEY(manager_id) REFERENCES employees(employee_id); Table altered.
11.11. Menghapus Constraint Untuk menghapus suatu Constraint, juga digunakan perintah ALTER TABLE. Cara penulisan : ALTER TABLE table DROP [CONSTRAINT constraint] type (column);
Untuk menghapus constraint manager dari tabel EMPLOYEES :
ALTER employees ALTER TABLE TABLE employees DROP CONSTRAINT emp_manager_fk; DROP CONSTRAINT emp_manager_fk; Table Table altered. altered. Untuk menghapus constraint PRIMARY KEY pada tabel departments dan menghapus asosiasi constraint FOREIGN KEY pada kolom EMPLOYEES.DEPARTMENT_ID
ALTER ALTER TABLE TABLE departments departments DROP PRIMARY KEY DROP PRIMARY KEY CASCADE; CASCADE; Table altered. Table altered. 11.12. Men-Disable Constraint BAB 11 : Constraint
Halaman : 11 - 4
Oracle9i Introduction to SQL
@Tessy Badriyah, SKom. MT.
Untuk menonaktifkan constraint digunakan klausa DISABLE. Pilihan CASCADE digunakan untuk men-disable dependent integrity constraint.
ALTER employees ALTER TABLE TABLE employees DISABLE CONSTRAINT DISABLE CONSTRAINT emp_emp_id_pk emp_emp_id_pk CASCADE; CASCADE; Table altered. Table altered. 11.13. Meng-Enable Constraint Untuk mengaktifkan kembali constraint, digunakan klausa ENABLE ALTER employees ALTER TABLE TABLE employees ENABLE CONSTRAINT emp_emp_id_pk; ENABLE CONSTRAINT emp_emp_id_pk; Table altered. Table altered. 11.14. Cascading Constraint Klausa CASCADE CONSTRAINTS digunakan dengan klausa DROP COLUMN. Klausa CASCADE CONSTRAINTS menghapus semua referential integrity constraints yang merefer ke primary dan unique key yang didefinisikan pada kolom yang dihapus. ALTER ALTER TABLE TABLE test1 test1 DROP (pk) DROP (pk) CASCADE CASCADE CONSTRAINTS; CONSTRAINTS; Table altered. Table altered.
ALTER ALTER TABLE TABLE test1 test1 DROP (pk, fk, DROP (pk, fk, col1) col1) CASCADE CASCADE CONSTRAINTS; CONSTRAINTS; Table altered. Table altered. 11.15. Menampilkan Constraint Tabel USER_CONSTRAINT menyimpan informasi tentang constraint. Kolom tipe constraint (constraint_type) dapat berisi : C untuk constraint CHECK, R untuk referential integrity atau FOREIGN KEY, P untuk PRIMARY KEY, U untuk constraint UNIQUE.
SELECT FROM WHERE
constraint_name, constraint_type, search_condition user_constraints table_name = 'EMPLOYEES';
…
11.16. Menampilkan Kolom yang Berasosiasi dengan suatu Constraint BAB 11 : Constraint
Halaman : 11 - 5
Oracle9i Introduction to SQL
@Tessy Badriyah, SKom. MT.
Berikut ini untuk menampilkan Kolom yang Berasosiasi dengan suatu nama suatu Constraint yang ada pada view USER_CONS_COLUMNS
SELECT FROM WHERE
constraint_name, column_name user_cons_columns table_name = 'EMPLOYEES';
… 11.17. Latihan 1. Tambahkan table-level PRIMARY KEY Constraint pada tabel EMP pada kolom ID. Constraint diberi nama : my_emp_id_pk 2. Buat PRIMARY KEY Constraint pada tabel DEPT dengan menggunakan kolom ID. Constraint diberi nama : my_dpet_id_pk 3. Tambahkan kolom DEPT_ID ke dalam tabel EMP. Tambahkan foreign key references pada tabel EMP yang memastikan bahwa data pegawai dimasukkan berdasarkan nomer departmetn yang ada pada tabel DEPT. 4. Tampilkan constraint-constraint yang telah ditambahkan dengan melihat pada view USER_CONSTRAINTS. 5. Tampilkan nama object dan tipenya dari data dictionary USER_OBJECTS untuk tabel EMP dan DEPT. 6. Modifikasi tabel EMP. Tambahkan kolom COMMISSION dengan tipe data NUMBER(2). Tambahkan Constraint untuk memastikan bahwa kolom ini harus diisi dengan nilai > 0.
BAB 11 : Constraint
Halaman : 11 - 6