CARA MENGGUNAKAN CONSTRAINT DI ORACLE 10G

CARA MENGGUNAKAN CONSTRAINT DI ORACLE 10G

   5:05 PM       Unknown

---

Constraint adalah batasan atau aturan yang ada pada table. Constraint mencegah penghapusan data dari suatu table yang mempunyai keterkaitan dengan table yang lain. Misalnya 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));

a. Constraint NOT NULL

Sebuah NOT NULL constraint didefinisikan di tingkat kolom, tetapi tidak dapat didefinisikan di tingkat tabel. Sintaks untuk NOT NULL constraint adalah sebagai berikut:

[CONSTRAINT <constraint name>] [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.

b.        Check Constraint

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),…

Sebuah cek constraint dapat didefinisikan pada kolom tabel tingkat atau level. Untuk kedua kolom dan tingkat tabel, sintaks adalah sebagai berikut:

[CONSTRAINT <constraint name>] CHECK ( <condition> )

Kondisi yang ditetapkan dalam klausal CHECK harus mengevaluasi suatu hasil Boolean dan dapat merujuk pada nilai-nilai dalam kolom lain baris yang sama. Fungsi lingkungan (seperti SYSDATE, USER, USERENV, dan UID) dan pseudo-kolom (seperti ROWNUM, CURRVAL, NEXTVAL, dan LEVEL) tidak dapat digunakan untuk mengevaluasi kondisi cek. Satu kolom dapat memiliki lebih dari satu CHECK  didefinisikan. Berikut ini adalah contoh dari kendala constrain didefinisikan di tingkat tabel:

CREATE TABLE bonus (

emp_id VARCHAR2 (40) NOT NULL,

salary NUMBER (9,2),

bonus NUMBER (9,2),

CONSTRAINT ck_bonus check (bonus > 0));

ALTER TABLE bonus

ADD CONSTRAINT ck_bonus2 CHECK (bonus < salary);

Cek constraint dapat didefinisikan pada tingkat kolom jika constraint hanya mengacu pada kolom tersebut. Anda tidak dapat menggunakan ALTER TABLE  klausul MODIFY untuk menambahkan atau memodifikasi constraint (hanya NOT NULL constraint dapat dimodifikasi dengan cara ini). Kolom- constraint tingkat dapat didefinisikan bila menggunakan pernyataan CREATE TABLE atau bila  menggunakan pernyataan ALTER TABLE ADD dengan klausa. Berikut ini sebuah contoh:

ALTER TABLE orders ADD cust_id number (5)

CONSTRAINT ck_cust_id CHECK (cust_id > 0);

Anda dapat menggunakan cek constraint untuk melaksanakan NOT NULL constraint juga. Hal ini sangat berguna jika Anda perlu untuk melarang nilai NULL di beberapa kolom bersama-sama. Sebagai contoh, berikut definisi constraint bagi tabel BONUS memungkinkan sebuah nilai NULL untuk kolom SALARY dan BONUS jika kolom kedua bernilai NULL, atau kedua kolom harus sudah berlaku nilai non-NULL.

ALTER TABLE bonus ADD CONSTRAINT ck_sal_bonus

CHECK ((bonus IS NULL AND salary IS NULL) OR

(bonus IS NOT NULL AND salary IS NOT NULL));

c.         Constraint Unik

Constraint yang unik melindungi satu atau lebih kolom dalam sebuah tabel, memastikan bahwa tidak ada dua baris berisi data ganda dalam kolom dilindungi. Constraint unik  dapat didefinisikan pada tingkat kolom satu kunci unik.  Berikut adalah tingkat kolom sintaks:

[CONSTRAINT <constraint name>] UNIQUE

Constraint harus didefinisikan di tingkat tabel. Berikut adalah tabel tingkat sintaks:

[CONSTRAINT <constraint name>]

UNIQUE (<column>, <column>, …)

d.         Primary Key Constraint

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));

Semua karakteristik kunci yang unik yang dapat diterapkan pada kunci primer constrain, kecuali nilai NULL tidak diperbolehkan dalam kolom kunci primer. Sebuah tabel hanya dapat memiliki satu primary key. Sintaks Tingkat kolom adalah sebagai berikut:

[CONSTRAINT <constraint name>] PRIMARY KEY

Here is the table-level syntax:

[CONSTRAINT <constraint name>]

PRIMARY KEY (<column>, <column>, …)

Oracle menciptakan indeks yang unik dan NOT NULL constrain untuk setiap kolom dalam kunci. Contoh berikut mendefinisikan primary key saat membuat tabel :

CREATE TABLE employee (dept_no VARCHAR2 (2),emp_id NUMBER (4),name VARCHAR2 (20) NOT NULL,ssn VARCHAR2 (11),salary NUMBER (9,2) CHECK (salary > 0),CONSTRAINT pk_employee primary key (dept_no, emp_id),CONSTRAINT uq_ssn unique (ssn))

Untuk menambah primary key untuk tabel yang ada, gunakan pernyataan ALTER TABLE. Berikut ini sebuah contoh:

ALTER TABLE employee

ADD CONSTRAINT pk_employee PRIMARY KEY (dept_no, emp_id);

Indeks diciptakan untuk menegakkan kunci unik dan kunci primer dapat dikelola dengan cara yang sama seperti indeks lainnya. Namun, indeks ini tidak dapat dihapuskan secara eksplisit menggunakan pernyataan DROP INDEX.

e.         Foreign Key Constraint

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. Contoh penggunaan Constraint Foreign Key :

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));

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.

f.         Disable Constrain

Untuk menonaktifkan constraint digunakan klausa DISABLE. Pilihan CASCADE digunakan untuk men-disable dependent integrity constraint.

ALTER TABLE employees

DISABLE CONSTRAINT emp_emp_id_pk CASCADE;

Source : https://satrs12.wordpress.com/