Menciptakan Table
Seperti telah kita pelajari sebelumnya bahwa sistem basis data Oracle memiliki berbagai macam struktur data. Table merupakan struktur data utama pada sistem basis data Oracle. Pada pembahasan kali ini kita akan membicarakan bagaimana menciptakan table di Oracle.
9.1. Menciptakan Table
Kita dapat menciptakan table dengan menggunakan perintah CREATE TABLE. Kita harus memiliki privilege CREATE TABLE untuk dapat menciptakan table serta harus memiliki area penyimpanan dimana objek tersebut diciptakan. Kita akan membahas masalah privilege pada bab tentang Mengontrol Akses User.
Sintaks
CREATE TABLE [ schema. ]table
( column datatype [ DEFAULT expr ] [ column_constraint ],
. . .
[ table_constraint ] );
Dimana:
- schema
- kumpulan objek, dimiliki oleh user sistem basis data dan memiliki nama yang sama dengan user tersebut.
- table
- nama table yang akan diciptakan.
- DEFAULT expr
- menentukan nilai default jika nilai tidak disebutkan pada perintah INSERT.
- column
- nama kolom.
- datatype
- tipe data dan panjang data dari kolom.
- column_constraint
- constraint sebagai bagian dari definisi kolom.
- table_constraint
- constraint sebagai bagian dari definisi tabel.
Aturan Penamaan
Pemberian nama pada table atau kolom harus memenuhi aturan-aturan berikut:
- Harus diawali dengan huruf dan memiliki panjang 1 – 30 karakter.
- Hanya dapat terdiri dari karakter A-Z, a-z, 0-9, _ (garis bawah), $ dan #.
- Tidak boleh sama dengan nama table lain dalam satu schema.
- Bukan merupakan reserved words Oracle.
Keterangan:
- Nama tabel atau kolom adalah case-insensitive (tidak membedakan huruf besar dan kecil). Sebagai contoh, EMPLOYEE memiliki arti yang sama dengan employee atau Employee.
9.2. Tipe Data
Terdapat banyak tipe data yang disediakan oleh sistem basis data Oracle. Tipe-tipe data utama adalah character, number, date, dan RAW.
- VARCHAR2(n)
- Karakter dengan panjang bervariasi dengan panjang maksimum n. Panjang minimum 1 dan maksimum 2000.
- CHAR(n)
- Karakter dengan panjang tetap dengan panjang maksimum n. Nilai default 1, panjang maksimum 255.
- NUMBER
- Angka dengan tingkat panjang sampai 38 digit.
- NUMBER(p,s)
- Angka dengan panjang maksimum p dengan jangkauan 1 sampai 38 digit dan s desimal.
- DATE
- Tanggal dan waktu antara 1 Januari 4712 S.M sampai 31 Desember 4712 M.
- LONG
- Karakter dengan panjang bervariasi hingga 2 gigabytes. Hanya satu kolom LONG yang diperbolehkan pada setiap table.
- RAW dan LONG RAW
- Sama dengan VARCHAR2 dan LONG, namun digunakan untuk menyimpan data byte-oriented atau binary misalnya image.
9.3. Constraint
Constraint berfungsi memaksakan aturan tertentu pada table atau kolom. Kita dapat menggunakan constraint untuk:
- Memaksakan aturan pada level table ketika dilakukan operasi insert, update atau delete terhadap table tersebut. Constraint harus dipenuhi agar operasi-operasi tersebut dapat dilakukan dengan sukses.
- Mencegah penghapusan terhadap table apabila terdapat ketergantungan dari table lain.
- Menyediakan aturan-aturan untuk development tools Oracle lainnya seperti Oracle Forms dan Reports.
9.3.1. Tipe-tipe Constraint
Terdapat berbagai tipe constraint yang dapat kita gunakan dalam definisi table sesuai dengan kebutuhan kita. Table berikut ini menunjukkan tipe-tipe constraint tersebut.
- NOT NULL
- Menyatakan bahwa suatu kolom tidak boleh mengandung nilai null.
- UNIQUE
- Menyatakan bahwa nilai suatu kolom harus unik (berbeda dengan nilai kolom pada baris yang lain).
- PRIMARY KEY
- Secara unik membedakan baris-baris data pada table.
- FOREIGN KEY
- Menyatakan relasi antara dua table.
- CHECK
- Menyatakan kondisi yang harus dipenuhi pada suatu kolom.
9.3.2. Ketentuan Penamaan Constraint
Seluruh constraint tersimpan pada data dictionary. Penamaan constraint yang baik memudahkan kita jika suatu saat kita akan memodifikasi atau menghapus constraint yang telah kita ciptakan. Jika kita tidak memberi nama pada constraint yang kita ciptakan, maka otomatis Oracle akan memberikan nama dengan format SYS_Cn, dimana n adalah nomor constraint.
Nama constraint sebaiknya memiliki format table_constraint_type. Sebagai contoh apabila kita ingin menciptakan constraint PRIMARY KEY untuk kolom ID dari tabel EMPLOYEE, maka nama constraint sebaiknya EMPLOYEE_ID_PK. Keterangan mengenai simbol yang disarankan untuk setiap jenis constraint dapat dilihat pada tabel berikut.
- Primary Key -> PK
- Unique -> UK
- Not Null -> NN
- Check -> CK
- Primary Key Kombinasi -> PKn
- Unique Kombinasi -> UKn
9.3.3. Menciptakan Constraint
Constraint biasanya diciptakan bersamaan dengan saat table diciptakan. Constraint dapat ditambahkan ke table setelah table sudah ada dan juga dapat dinonaktifkan sementara. Pembahasan mengenai hal ini akan kita jumpai pada bab Memodifikasi Table dan Constraint.
9.3.4. Mendefinisikan Constraint
Constraint dapat didefinisikan pada salah dua level berbeda.
- Column Constraint
- Didefinisikan bersama-sama dengan definisi kolom yang memilikinya. Pada level ini kita dapat mendefinisikan seluruh tipe constraint.
- Table Constraint
- Didefinisikan terpisah dari definisi kolom yang memilikinya. Pada level ini kita dapat mendefinisikan seluruh tipe constraint kecuali NOT NULL.
Sintaks – Constraint Level Kolom
column [CONSTRAINT constraint_name] constraint_type
Sintaks – Constraint Level Kolom
column,
[CONSTRAINT constraint_name] constraint_type
(column, …),
Contoh:
Mendefinisikan constraint PRIMARY KEY (level kolom) untuk kolom ID dari table REGION.
CREATE TABLE region
(id NUMBER(7)
CONSTRAINT region_id_pk PRIMARY KEY,
name VARCHAR2(50)
CONSTRAINT_region_name_nn NOT NULL
CONSTRAINT region_name_uk UNIQUE);
Mendefinisikan constraint PRIMARY KEY (level tabel) untuk kolom ID dari table REGION.
CREATE TABLE region
(id NUMBER(7),
name VARCHAR2(50)
CONSTRAINT_region_name_nn NOT NULL
CONSTRAINT region_name_uk UNIQUE,
CONSTRAINT region_id_pk PRIMARY KEY(id));
9.3.5. Constraint NOT NULL
Constraint NOT NULL melarang nilai null untuk suatu kolom. Kolom tanpa constraint NOT NULL secara default dapat mengandung nilai null. Constraint ini hanya dapat didefinisikan pada level kolom.
Contoh:
Menentukan constraint NOT NULL pada kolom PHONE.
CREATE TABLE friend …
phone VARCHAR2(15) NOT NULL
Menentukan constraint NOT NULL pada kolom LAST_NAME. Constraint diberi nama FRIEND_LAST_NAME_NN.
CREATE TABLE friend …
last_name VARCHAR2(15)
CONSTRAINT friend_last_name_nn NOT NULL, …
9.3.6. Constraint UNIQUE
- Constraint UNIQUE menandakan bahwa kolom atau kombinasi kolom bernilai unik. Constraint ini melarang adanya nilai duplikat.
- Nilai null diperbolehkan apabila kunci unik berbasis kolom tunggal.
- Constraint UNIQUE dapat didefinisikan pada constraint level kolom atau level tabel.
- Kunci unik yang terdiri dari beberapa kolom diciptakan pada definisi level table.
- UNIQUE index otomatis diciptakan untuk kolom yang memiliki constraint ini.
Contoh:
Mendefinisikan constraint UNIQUE untuk kolom NAME dari tabel REGION.
CREATE TABLE region …
name VARCHAR2(50)
CONSTRAINT region_name_uk UNIQUE, …
Jika constraint tersebut didefinisikan pada level table maka perintahnya menjadi sebagai berikut.
CREATE TABLE employee …
name VARCHAR2(50), …
CONSTRAINT region_name_uk UNIQUE(name), …
9.3.7. Constraint PRIMARY KEY
- Constraint PRIMARY KEY menciptakan kunci utama pada table. Satu table hanya boleh memiliki satu kunci utama.
- Constraint PRIMARY KEY adalah kolom atau kombinasi kolom yang secara unik membedakan baris data satu dengan lainnya dalam suatu table.
- Constraint ini memastikan bahwa nilai kolom yang merupakan bagian dari kunci utama haruslah unik dan tidak mengandung nilai null.
- Constraint PRIMARY KEY dapat didefinisikan pada constraint level kolom atau level table. Kunci utama yang terdiri dari beberapa kolom diciptakan pada definisi level table.
- UNIQUE index otomatis diciptakan untuk kolom yang memiliki constraint ini.
Contoh:
Mendefinisikan constraint PRIMARY KEY untuk kolom ID dari table EMPLOYEE.
CREATE TABLE employee …
id NUMBER(7)
CONSTRAINT employee_id_pk PRIMARY KEY, …
Jika constraint tersebut didefinisikan pada level table maka perintahnya menjadi sebagai berikut.
CREATE TABLE employee …
id NUMBER(7), …
CONSTRAINT employee_id_pk PRIMARY KEY (id), …
9.3.8. Constraint FOREIGN KEY
Constraint FOREIGN KEY menandakan bahwa kolom atau kombinasi kolom sebagai kunci tamu dan membentuk hubungan antara kunci utama atau kunci unik dalam table yang sama atau antara beberapa table.
Kunci tamu harus memiliki nilai sama dengan table induk yang diacunya atau bernilai null.
Constraint FOREIGN KEY dapat didefinisikan pada constraint level kolom atau level table. Kunci tamu yang terdiri dari beberapa kolom diciptakan pada definisi level table.
Kunci tamu yang merupakan bagian dari kunci utama tidak boleh bernilai null karena tidak ada satupun bagian dari kunci utama boleh bernilai null.
Kunci tamu didefinisikan pada table anak, dan mengandung kolom yang diacunya pada table induk. Kunci tamu didefinisikan dengan kombinasi perintah-perintah berikut:
- FOREIGN KEY digunakan untuk mendefinisikan kolom pada table anak pada level constraint kolom.
- REFERENCES menentukan table dan dan kolom pada table induk.
- ON DELETE CASCADE menyatakan bahwa ketika baris data pada table induk dihapus, maka baris data pada table anak yang mengacu kepadanya juga akan dihapus.
Tanpa opsi ON DELETE CASCADE, baris data pada table induk tidak dapat dihapus jika masih ada data dari table anak yang mengacu kepadanya.
Contoh:
Mendefinisikan constraint FOREIGN KEY untuk kolom DEPT_ID dari table EMPLOYEE yang mengacu kepada kolom ID dari table DEPARTMENT.
CREATE TABLE employee …
dept_id NUMBER(7)
CONSTRAINT employee_dept_id_fk REFERENCES department(id), …
Jika constraint tersebut didefinisikan pada level table maka perintahnya menjadi sebagai berikut.
CREATE TABLE employee …
dept_id NUMBER(7), …
CONSTRAINT employee_dept_id_fk FOREIGN KEY (dept_id)
REFERENCES department(id), …
9.3.9. Constraint CHECK
Constraint CHECK mendefinisikan kondisi yang harus dipenuhi oleh setiap baris data. Kondisi dapat menggunakan bentuk yang sama dengan kondisi-kondisi query dengan perkecualian:
- Mengacu pada kolom CURRVAL, NEXTVAL, LEVEL, atau ROWNUM.
- Memanggil fungsi SYSDATE, UID, USER, atau USERENV.
- Query yang mengacu kepada nilai dari baris data lain.
Constraint CHECK dapat didefinisikan pada constraint level kolom atau level table.
Contoh:
Mendefinisikan constraint CHECK untuk kolom COMMISSION_PCT dari table EMPLOYEE.
CREATE TABLE employee …
commission_pct NUMBER(4,2)
CONSTRAINT employee_commission_pct_ck
CHECK (commission_pct IN (10, 12.5, 15, 17.5, 20)), …
Jika constraint tersebut didefinisikan pada level table maka perintahnya menjadi sebagai berikut.
CREATE TABLE employee …
commission_pct NUMBER(4,2), …
CONSTRAINT employee_commission_pct_ck
CHECK (commission_pct IN (10, 12.5, 15, 17.5, 20)), …
9.4. Menciptakan Tabel dari Table Instance Chart
Metode pertama untuk menciptakan table adalah berdasarkan table instance chart (definisi table) yang telah kita buat sebelumnya.
Contoh:
Menciptakan table DEPARTMENT berdasarkan table instance chart.
| Column | Datatype | Length | Constraint | FK Table | FK Column |
| ID | NUMBER | 7 | PK, NN, UK | ||
| NAME | VARCHAR2 | 25 | NN, UK1 | ||
| REGION_ID | NUMBER | 7 | UK1, FK | REGION | ID |
CREATE TABLE department
(id NUMBER(7)
CONSTRAINT department_id_pk PRIMARY KEY,
name VARCHAR2(25)
CONSTRAINT department_name_nn NOT NULL,
region_id NUMBER(7)
CONSTRAINT department_region_id_fk
FOREIGN KEY (region_id) REFERENCES region (id),
CONSTRAINT department_name_region_id_uk
UNIQUE (name, region_id));
Pembahasan
- Constraint level kolom DEPARTMENT_ID_PK menentukan kolom ID sebagai kunci utama dari table DEPARTMENT.
- Constraint level kolom DEPARTMENT_NAME_NN memastikan bahwa kolom NAME tidak bernilai null.
- Constraint level kolom DEPARTMENT_REGION_ID_FK memastikan bahwa setiap kode region yang diinputkan pada table DEPARTMENT memiliki nilai pada table induknya yaitu REGION. Sebelum mendefinisikan constraint ini, kolom ID dari tabel REGION harus memiliki constraint PRIMARY KEY atau UNIQUE.
- Constraint level table DEPARTMENT_NAME_REGION_ID_UK memastikan bahwa kombinasi nilai kolom NAME dan REGION_ID adalah unik.
Oracle Magazine contains technology strategy articles, sample code, tips, Oracle and partner news, how to articles for developers and DBAs, and more. Oracle (NASDAQ: ORCL) is the world’s largest enterprise software company.
For more information about Oracle, please visit their Web site at http://www.oracle.com. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
9.5. Menciptakan Table dengan Subquery
Metode lain untuk menciptakan table adalah dengan subquery. Hal ini dapat diartikan menciptakan table baru dengan cara mengkopi struktur dan data dari table lain.
Sintaks
CREATE TABLE table
[column(, column …)]
AS subquery
Dimana:
- table
- nama table yang akan diciptakan.
- column
- nama kolom, nilai default, dan constraint.
- subquery
- perintah SELECT yang mendefinisikan baris data yang akan diinputkan ke table baru.
Aturan
- Table diciptakan sesuai nama-nama kolom yang ditentukan, dan seluruh baris data yang ditampilkan oleh subquery akan dimasukkan ke table yang baru diciptakan tersebut.
- Definisi kolom tidak dapat mengandung definisi kunci tamu.
- Bila nama kolom disebutkan, jumlah nama kolom harus sama dengan jumlah kolom dalam subquery.
- Bila tidak disebutkan nama kolom, maka nama kolom diberikan sesuai dengan nama kolom pada subquery.
- Hanya constraint NOT NULL yang diturunkan dari table pada subquery kepada table baru.
Contoh:
Menciptakan table yang berisi data karyawan yang bekerja pada departemen 41.
CREATE TABLE employee41
AS
SELECT id, last_name, userid, start_date
FROM employee
WHERE dept_id = 41;
9.6. Mengkonfirmasi Table
Setelah tabel diciptakan maka informasi table tersebut akan tersimpan dalam data dictionary (kamus data) Oracle, sama seperti objek-objek lainnya. Kita dapat melihatnya menggunakan table USER_OBJECTS atau USER_TABLES.
Contoh:
Menampilkan informasi dari seluruh table telah kita ciptakan.
SELECT table_name
FROM user_tables;
Menampilkan struktur table DEPARTMENT.
DESCRIBE department
9.7. Menghapus Table
Gunakan perintah DROP TABLE untuk menghapus table. Perintah ini hanya dapat dilakukan oleh pemilik table atau user yang memiliki system privilege DROP ANY TABLE.
Sintaks
DROP TABLE table
Silahkan melanjutkan membaca pembahasan Oracle Data Dictionary
Catatan:
Untuk mempraktekkan artikel-artikel mengenai Oracle SQL & PL/SQL Anda dapat mendownload script table disini, lalu jalankan sesuai live demo (browser Anda harus mendukung Flash Player).
 |
Buku ini membahas berbagai macam tip dan trik yang sangat berguna bagi Anda pembaca pengguna database Oracle. Pemula, mahasiswa, programmer, maupun database administrator yang mengharapkan solusi cepat dalam menangani permasalahan-permasalahan dalam penggunaan database Oracle dapat membaca buku ini. Anda tidak harus membaca buku ini secara urut karena buku ini di susun berdasarkan topik permasalahan, dan Anda dapat membaca topik-topik yang sesuai dengan permasalahan yang Anda hadapi. Dapat di beli di toko buku Gramedia, Gunung Agung, Uranus, Karisma dsb. Harga Rp. 24.800,- (157 halaman) |
[…] Silahkan melanjutkan membaca pembahasan Menciptakan Table […]