SQLite : Query untuk Membuat Tabel (CREATE TABLE)
![SQLite : Query untuk Membuat Tabel (CREATE TABLE)](https://sainsdata.id/wp-content/uploads/2022/09/Picture1.png)
![SQLite : Query untuk Membuat Tabel (CREATE TABLE)](https://sainsdata.id/wp-content/uploads/2022/09/Picture1.png)
Structured Query Language (SQL) adalah bahasa pemrograman yang digunakan untuk mengelola dan memanipulasi basis data relasional. SQL memungkinkan pengguna untuk membuat, membaca, memperbarui, dan menghapus data yang tersimpan dalam sistem manajemen basis data.
Salah satu implementasi dari SQL adalah SQLite, yaitu perangkat lunak yang menyediakan sistem manajemen basis data relasional yang ringan dan cepat. Berbeda dengan sistem basis data lain yang membutuhkan server terpisah, SQLite menyimpan seluruh basis data dalam satu file di perangkat lokal, menjadikannya ideal untuk aplikasi yang memerlukan penyimpanan data sederhana dan efisien. SQLite sering digunakan dalam pengembangan aplikasi mobile, perangkat lunak embedded, dan berbagai proyek yang memerlukan solusi basis data yang tidak memerlukan konfigurasi server yang kompleks.
Pada tutorial kali ini, kita akan mempelajari cara membuat tabel pada database SQLite. Walaupun demikian, secara umum sintaks yang serupa dapat diterapkan dalam berbagai DBMS berbasis SQL. Selain membuat tabel, juga akan diperlihatkan bagaimana menetapkan primary key, foreign key, constrains maupun check condition pada tabel tersebut.
Untuk membuat tabel pada database SQLite, terdapat 2 struktur query yang dapat digunakan yaitu:
SQL
CREATE TABLE [IF NOT EXISTS] [nama_db].nama_tabel ( kolom_1 tipe PRIMARY KEY, kolom_2 tipe REFERENCES tabel_referensi (kolom_referensi), kolom_3 tipe NOT NULL, ... kolom_4 tipe DEFAULT 0, kolom_5 tipe CHECK(kondisi), ) [WITHOUT ROWID];
- Langkah pertama yang dilakukan adalah menetapkan nama tabel yang akan dibuat menggunakan
CREATE TABLE
- Adapun
IF NOT EXISTS
merupakan tambahan opsional, untuk menghindari error jika tabel dengan nama tersebut sudah ada. Jika menggunakan sintaks ini maka query tidak dieksekusi jika tabel sudah ada. - Dapat pula dituliskan secara eksplisit nama database sebelum nama tabel diikuti tanda titik (
.
) dan nama tabel. - Selanjutnya ditentukan nama-nama kolom yang akan dibuat, serta konstrain pada masing-masing kolom, seperti
PRIMARY KEY
,REFERENCES
(foreign key),NOT NULL
,DEFAULT
,UNIQUE
, danCHECK
. PRIMARY KEY
, menunjukkan kolom yang menjadi identitas dari suatu tabel, misalkan NIM pada data Mahasiswa.REFERENCES
, menunjukkan kolom yang terkait dengan kolom pada tabel lain sebagai referensiNOT NULL
, mengindikasikan kolom tersebut tidak boleh kosongDEFAULT
, menetapkan nilai default pada kolom, ketika tidak diberikan secara spesifik saat menyimpan data maka nilai default ini akan digunakanUNIQUE
, kolom dengan konstrain ini harus bersifat unik (tidak boleh ada yang sama)CHECK
, digunakan untuk memberikan batasan nilai/kondisi tertentu yang harus dipenuhi pada kolom tersebutWITHOUT ROWID
, secara implisit SQLite sebenarnya membuatrowid
,oid
, atau_rowid_
untuk setiap baris data, sehingga berfungsi semacam identitas. Dengan kondisi ini, walaupun kita menetapkan suatu kolom sebagaiPRIMARY KEY
dimana seharusnya bersifatUNIQUE
danNOT NULL
, namun kolom tersebut tetap bisa berisiNULL
. Sejak SQLite 3.8.2, untuk menghindari kondisi ini kita dapat secara eksplisit membuat tabel tanparowid
menggunakan keywordWITHOUT ROWID
.
Selain struktur di atas, terdapat alternatif struktur dimana konstrain PRIMARY KEY
dan REFERENCES
diletakkan setelah seluruh nama kolom didefinisikan, seperti berikut ini:
SQL
CREATE TABLE nama_tabel ( kolom_1 tipe, kolom_2 tipe, kolom_3 tipe NOT NULL, ... kolom_4 tipe DEFAULT 0, kolom_5 tipe CHECK(kondisi), PRIMARY KEY("kolom_pk1", ...), FOREIGN KEY("kolom_fk") REFERENCES "tabel_rujukan"("kolom_rujukan") );
Contoh SQLite CREATE TABLE
Sebagai ilustrasi kita akan membuat sebuah database konten blog yang sederhana dan hanya 3 tabel saja. Masing-masing tabel yaitu tabel user
untuk menyimpan data user, tabel post
untuk data post dan tabel comment
untuk menyimpan informasi komentar pada suatu post.
Rancangan tabel yang akan dibuat kurang lebih seperti gambar berikut ini:
![ERD](https://sainsdata.id/wp-content/uploads/2022/09/er.png)
![ERD](https://sainsdata.id/wp-content/uploads/2022/09/er.png)
Tabel User
Untuk membuat tabel user
kita menggunakan query sebagai berikut:
SQL
CREATE TABLE user ( user_id VARCHAR (20) PRIMARY KEY, email VARCHAR (20), password VARCHAR (64), level VARCHAR (7) );
Pada sintaks di atas, kita hanya menentukan kolom-kolom pada tabel user
dimana user_id
sebagai PRIMARY KEY
. Sementara itu kolom-kolom lainnya hanya kita tentukan tipe datanya saja, misal untuk email bertipe VARCHAR
dengan panjang maksimal 20 karakter. Pada kenyataannya mungkin terdapat konstrain tertentu yang harus dipenuhi pada tiap-tiap kolom. Untuk itu kita dapat menggunakan berbagai konstrain yang tersedia pada SQLite.
Misalkan dalam contoh ini, tabel user
harus mengikuti ketentuan sebagai berikut:
- Kolom
user_id
harus terdiri lebih dari 4 karakter. - Kolom email harus memiliki format
xxx@yyy.zzz
dan kemudian tidak boleh kosong. Selain itu satu alamat email hanya bisa dimiliki oleh satuuser
saja sehingga harus unik dan tidak boleh ada duauser
memiliki alamat email yang sama. - Kolom
password
harus terdiri dari minimal 8 karakter dan maksimal 64 karakter dan tidak boleh kosong. - level harus dipilih antara (
newbie
,standar
,master
) dengan nilai awal/default adalahnewbie
.
Untuk memenuhi kriteria tersebut, tabel user
dapat kita definisikan ulang menggunakan query berikut ini:
SQL
CREATE TABLE user ( user_id VARCHAR (20) PRIMARY KEY CHECK (length(user_id) > 4), email VARCHAR (20) UNIQUE NOT NULL CHECK (email LIKE '%_@_%._%'), password VARCHAR (64) NOT NULL CHECK (length(password) > 8 AND length(password) <= 64), level VARCHAR (7) NOT NULL DEFAULT 'Newbie' CHECK (level IN ('Newbie', 'Standar', 'Master')) );
Tabel Post
Selanjutnya untuk tabel post
memiliki ketentuan berikut:
- Kolom
post_id
merupakanPRIMARY KEY
, serta nilainya tidak kita tentukan sendiri melainkan secara otomatis berdasarkan nomor urutpost
yang ada ( post pertama akan memilikipost_id = 1
, post ke-2 memilikipost_id = 2
, dst) atauAUTOINCREMENT
. - Kolom
post_title
tidak boleh kosong dengan nilai default adalah ‘Post Title
‘. post_content
tidak ada aturan khusus.- Kolom
post_author
tidak boleh kosong dan berisiuser_id
dari user yang menulis post tersebut, sehingga harus sesuai dengan daftaruser_id
yang ada pada tabeluser
. - Kolom
post_like
berisi jumlah like dari post tersebut dan nilainya lebih besar sama dengan 0 dan nilai default = 0.
Query berikut merupakan implementasi dari kriteria di atas.
SQL
CREATE TABLE post ( post_id INTEGER PRIMARY KEY AUTOINCREMENT, post_title VARCHAR (100) NOT NULL DEFAULT ('Post Title'), post_content TEXT, post_author VARCHAR (20) REFERENCES user (user_id) NOT NULL, post_like INTEGER (3) DEFAULT (0) CHECK (like_count >= 0) );
Perhatikan pada kolom post_author
, kewyord REFERENCES
menunjukkan bahwa kolom post_author
merujuk pada kolom user_id
pada tabel user
. Karena kolom ini merujuk pada tabel lain, tentu saja jika terdapat perubahan atau penghapusan user_id
maka akan berpengaruh pada isian tabel post
. Pada contoh sintaks di atas, jika user_id
tertentu terdapat pada post_author
, maka user
tersebut tidak bisa dihapus atau diupdate isian user_id
-nya.
Perlakuan lain dapat diberikan menggunakan sintaks ON UPDATE
atau ON DELETE
. Adapun beberapa aksi yang dapat diberikan yaitu:
NO ACTION
: tidak dilakukan apa-apa, sehingga update atau delete tidak dapat dilakukan (sama seperti tanpa dibaerikan perlakuan apapun)SET NULL
: jika data pada tabel rujukan (tabeluser
) dihapus atau update, maka isian pada tabel yang merujuk (tabelpost
) di set menjadiNULL
(jika boleh NULL, jika tidak maka akan error)SET DEFAULT
: jika data pada tabel rujukan (tabeluser
) dihapus atau update, maka isian pada tabel yang merujuk (tabelpost
) di-set menjadi nilai defaultnya (jika ada)CASCADE
: jika data pada tabel rujukan (tabeluser
) dihapus atau update, maka isian pada tabel yang merujuk (tabelpost
) akan ikut dihapus atau di-update. Misalkan user A memiliki 10 isian post pada tabelpost
, kemudian user A tersebut dihapus dari tabeluser
, maka semua post yang berkaitan dengan user A juga akan dihapus dari tabelpost
. Dalam kasus update, maka isian kolompost_author
akan ter-update sesuai update padauser_id
di tabeluser
.RESTRICT
: hampir mirip denganNO ACTION
dimana proses delete atau update tidak dapat dilakukan pada tabel yang jadi rujukan (tabeluser
).
SQL
CREATE TABLE post ( post_id INTEGER PRIMARY KEY AUTOINCREMENT, post_title VARCHAR (100) NOT NULL DEFAULT ('Post Title'), post_content TEXT, post_author VARCHAR (20) NOT NULL REFERENCES user (user_id) ON DELETE RESTRICT ON UPDATE CASCADE, post_like INTEGER (3) DEFAULT (0) CHECK (like_count >= 0) );
Query di atas mengindikasikan jika user_id
pada tabel user
yang muncul pada tabel post
menjadi tidak dapat dihapus, sementara jika user_id
pada tabel user
di-update, maka post_author
pada tabel post
juga akan di-update.
Tabel Comment
Selanjutnya untuk tabel comment
memiliki ketentuan berikut:
- Kolom
comment_id
merupakanPRIMARY KEY
danAUTOINCREMENT
. - Kolom
post_id
tidak boleh kosong dan merujuk pada kolompost_id
pada tabelpost
, menunjukkan post yang diberikan komentar. - Kolom
commentor_id
tidak boleh kosong dan berisiuser_id
dari user yang memberikan komentar dan merujuk pada kolomuser_id
pada tabeluser
. - Kolom
comment_content
berisi isian komentar (minimal panjang komentar 10 karakter).
Query berikut merupakan implementasi dari kriteria untuk tabel comment
.
SQL
CREATE TABLE comment ( comment_id INTEGER PRIMARY KEY AUTOINCREMENT, post_id INTEGER REFERENCES post (post_id) ON DELETE RESTRICT ON UPDATE CASCADE NOT NULL, commenter_id VARCHAR (20) REFERENCES user (user_id) ON DELETE RESTRICT ON UPDATE CASCADE NOT NULL, comment_content TEXT CHECK (length(comment_content) >= 10) );
Pada tabel comment
terdapat dua kolom yang merujuk pada tabel lain yaitu post_id
merujuk pada post_id
pada tabel post
, dan commenter_id
merujuk kolom user_id
pada tabel user
. Kita dapat memberikan kondisi ON DELETE
atau ON UPDATE
ataupun tidak, sesuai dengan kebutuhan.
Reformat Query
Seperti yang sudah dijelaskan pada awal tulisan, terdapat dua struktur penulisan query yang dapat digunakan untuk CREATE TABLE
. Berikut ini adalah query pembuatan ketiga tabel dimana PRIMARY KEY
dan FOREIGN KEY
didefiniskan di bagian akhir query.
SQL
-- TABEL USER CREATE TABLE user ( user_id VARCHAR (20) CHECK (length(user_id) > 4), email VARCHAR (20) UNIQUE NOT NULL CHECK (email LIKE '%_@_%._%'), password VARCHAR (64) NOT NULL CHECK (length(password) > 6 AND length(password) <= 64), level VARCHAR (7) NOT NULL DEFAULT 'Newbie' CHECK (level IN ('Newbie', 'Standar', 'Master')), PRIMARY KEY (user_id) ); -- TABEL POST CREATE TABLE post ( post_id INTEGER AUTOINCREMENT, post_title VARCHAR (100) NOT NULL DEFAULT ('Post Title'), post_content TEXT, post_author VARCHAR (20) NOT NULL post_like INTEGER (3) DEFAULT (0) CHECK (like_count >= 0), PRIMARY KEY (post_id), FOREIGN KEY (post_author) REFERENCES user (user_id) ON DELETE RESTRICT ON UPDATE CASCADE ); -- TABEL COMMENT CREATE TABLE comment ( comment_id INTEGER AUTOINCREMENT, post_id INTEGER NOT NULL, commenter_id VARCHAR (20) NOT NULL, comment_content TEXT CHECK (length(comment_content) >= 10), PRIMARY KEY (comment_id), FOREIGN KEY (post_id) REFERENCES post (post_id) ON DELETE RESTRICT ON UPDATE CASCADE, FOREIGN KEY (commenter_id) REFERENCES user (user_id) ON DELETE RESTRICT ON UPDATE CASCADE );
Sebagai catatan, pada struktur di atas, kita dapat membuat tabel yang memiliki lebih dari 1 PRIMARY KEY
, misalkan PRIMARY KEY (PK_1, PK_2)
.
Pada tutorial ini kita sudah membahas mengenai query untuk membuat tabel pada SQLite menggunakan CREATE TABLE
. Adapun untuk query lainnya yaitu SELECT
, UPDATE
dan DELETE
akan dibahas pada tutorial berikutnya.
Selanjutnya: Menguasai Perintah SELECT – SQLite