Membuat Table dan Constraint di Oracle

Tabel adalah objeck yg digunakan untuk menyimpan data.

Untuk menerapkan aturan di tabel digunakan CONSTRAINT.

 CONSTRAINT adalah aturan yang diterapkan di tabel untuk menjaga konsistensi dan integritas data.

Constraint bisa didefinisikan bersamaan dengan perintah CREATE TABLE atau setelah tabel terbentuk dengan perintah ALTER TABLE

Constraint ada 5 jenis

- Primary Key

- Unique

- Not null

- Check

- Foregn Key

Tabel dan constraint yang akan dibuat ada di PAPAN TULIS.

CREATE TABLE jurusan (

kode         VARCHAR2(3),

nama_jrs     VARCHAR2(30)

             CONSTRAINT nn_nama_jrs NOT NULL);

CREATE TABLE mahasiswa (

nim          NUMBER(4) CONSTRAINT pk_mhs PRIMARY KEY,

nama         VARCHAR2(15) DEFAULT 'Noname' NOT NULL,

jk           CHAR(1) CONSTRAINT ck_jk CHECK(jk IN ('L','P')),

email        VARCHAR2(25),

tgl_masuk    DATE DEFAULT sysdate);

ALTER TABLE mahasiswa

ADD CONSTRAINT fk_kode_jrs FOREIGN KEY (jrs)

REFERENCES jurusan(kode);

--> Error karena kolom KODE di tabel Jurusan belum dijadikan

    Primary key atau Unique

ALTER TABLE jurusan

ADD CONSTRAINT pk_jurusan PRIMARY KEY(kode);

ALTER TABLE mahasiswa

ADD CONSTRAINT fk_kode_jrs FOREIGN KEY (jrs)

REFERENCES jurusan(kode); --> berhasil

-- Pastikan kolom tidak ada email mahasiswa yg sama

ALTER TABLE mahasiswa

ADD CONSTRAINT uq_email UNIQUE(email);

Cara ngedrop constraint

ALTER TABLE mahasiswa

DROP CONSTRAINT fk_kode_jrs;

bagaimana mengetahui constraint2 yg ada di suatu tabel

SELECT constraint_name,

       constraint_type,

       table_name

FROM user_constraints

WHERE table_name IN ('MAHASISWA','JURUSAN');

CONSTRAINT_NAME                C TABLE_NAME

------------------------------ - ------------

PK_JURUSAN                     P JURUSAN

SYS_C005474                    C MAHASISWA

CK_JK                          C MAHASISWA

PK_MHS                         P MAHASISWA

FK_KODE_JRS                    R MAHASISWA

UQ_EMAIL                       U MAHASISWA

-- Buat lagi dengan ON DELETE SET NULL

ALTER TABLE mahasiswa

ADD CONSTRAINT fk_kode_jrs FOREIGN KEY(jrs)

REFERENCES jurusan(kode) ON DELETE SET NULL;

SQL> DELETE jurusan WHERE kode='MI';

1 row deleted.

SQL> SELECT * FROM jurusan;

KOD NAMA_JRS

--- ------------------------------

TI  Teknik Informatika

SQL> SELECT * FROM mahasiswa;

       NIM NAMA            J EMAIL                     JRS TGL_MASUK

---------- --------------- - ------------------------- --- ---------

         1 BUDI            L budi@yh.com                   16-AUG-11

         2 Nina            P nani@yh.com               TI  10-AUG-11

rollback;

ON DELETE CASCADE

------------------

ALTER TABLE mahasiswa

DROP CONSTRAINT fk_kode_jrs;

ALTER TABLE mahasiswa

ADD CONSTRAINT fk_kode_jrs FOREIGN KEY(jrs)

REFERENCES jurusan(kode) ON DELETE CASCADE;

SQL> ALTER TABLE mahasiswa

  2  DROP CONSTRAINT fk_kode_jrs;

Table altered.

SQL> ALTER TABLE mahasiswa

  2  ADD CONSTRAINT fk_kode_jrs FOREIGN KEY(jrs)

  3  REFERENCES jurusan(kode) ON DELETE CASCADE;

Table altered.

SQL> SELECT * FROM mahasiswa;

       NIM NAMA            J EMAIL                     JRS TGL_MASUK

---------- --------------- - ------------------------- --- ---------

         1 BUDI            L budi@yh.com               MI  16-AUG-11

         2 Nina            P nani@yh.com               TI  10-AUG-11

SQL> SELECT * FROM jurusan;

KOD NAMA_JRS

--- ------------------------------

MI  Manajemen Informatika

TI  Teknik Informatika

SQL> DELETE jurusan WHERE kode='MI';

1 row deleted.

SQL> SELECT * FROM jurusan;

KOD NAMA_JRS

--- ------------------------------

TI  Teknik Informatika

SQL> SELECT * FROM mahasiswa;

       NIM NAMA            J EMAIL                     JRS TGL_MASUK

---------- --------------- - ------------------------- --- ---------

         2 Nina            P nani@yh.com               TI  10-AUG-11

Membuat tabel dengan Subquery

-----------------------------

Buatlah copy dari data pegawai yang berada di deptno 10 dan 30

Gunakan Create Table dengan subquery

CREATE TABLE copy_emp1030

AS
SELECT * FROM emp

WHERE deptno IN(10,30);

SET LINESIZE 120

SELECT * FROM copy_emp1030;

EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

----- ---------- --------- ---------- --------- ---------- ---------- ----------

 7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30

 7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30

 7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30

 7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30

 7782 CLARK      MANAGER         7839 09-JUN-81       3000                    10

 7839 KING       PRESIDENT            17-NOV-81       5000                    10

 7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30

 7900 JAMES      CLERK           7698 03-DEC-81        950                    30

 7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

Menambah Kolom Baru

-------------------

Tambahkan kolom ALAMAT ke tabel MAHASISWA

Sebelum :

DESC mahasiswa;

ALTER TABLE mahasiswa

ADD (alamat    VARCHAR2(30));

Sesudah :

DESC mahasiswa;

-- Beri nilai default untuk alamat 'BANDUNG'

ALTER TABLE mahasiswa

MODIFY alamat DEFAULT 'BANDUNG');

-- Kolom nama_jrs tadinya NOT NULL, ubah menjadi boleh null

ALTER TABLE jurusan

MODIFY nama_jrs NULL;

-- Ganti nama kolom NAMA menjadi NAMA_MHS di tabel MAHASISWA

DESC mahasiswa;

ALTER TABLE mahasiswa

RENAME COLUMN nama TO nama_mhs;

DESC mahasiswa;

-- Hapus kolom email

ALTER TABLE mahasiswa

DROP COLUMN email;