SQLite : Query untuk Membuat Tabel (CREATE TABLE)

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:

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, dan CHECK.
• 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 referensi
• NOT NULL, mengindikasikan kolom tersebut tidak boleh kosong
• DEFAULT, menetapkan nilai default pada kolom, ketika tidak diberikan secara spesifik saat menyimpan data maka nilai default ini akan digunakan
• UNIQUE, 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 tersebut
• WITHOUT ROWID, secara implisit SQLite sebenarnya membuat rowid, oid, atau _rowid_ untuk setiap baris data, sehingga berfungsi semacam identitas. Dengan kondisi ini, walaupun kita menetapkan suatu kolom sebagai PRIMARY KEY dimana seharusnya bersifat UNIQUE dan NOT NULL, namun kolom tersebut tetap bisa berisi NULL. Sejak SQLite 3.8.2, untuk menghindari kondisi ini kita dapat secara eksplisit membuat tabel tanpa rowid menggunakan keyword WITHOUT ROWID.

Selain struktur di atas, terdapat alternatif struktur dimana konstrain PRIMARY KEY dan REFERENCES diletakkan setelah seluruh nama kolom didefinisikan, seperti berikut ini:

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

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:

Untuk membuat tabel user kita menggunakan query sebagai berikut:

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 satu user saja sehingga harus unik dan tidak boleh ada dua user 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 adalah newbie.

Untuk memenuhi kriteria tersebut, tabel user dapat kita definisikan ulang menggunakan query berikut ini:

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

Selanjutnya untuk tabel post memiliki ketentuan berikut:

• Kolom post_id merupakan PRIMARY KEY, serta nilainya tidak kita tentukan sendiri melainkan secara otomatis berdasarkan nomor urut post yang ada ( post pertama akan memiliki post_id = 1, post ke-2 memiliki post_id = 2, dst) atau AUTOINCREMENT.
• 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 berisi user_id dari user yang menulis post tersebut, sehingga harus sesuai dengan daftar user_id yang ada pada tabel user.
• 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.

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 (tabel user) dihapus atau update, maka isian pada tabel yang merujuk (tabel post) di set menjadi NULL (jika boleh NULL, jika tidak maka akan error)
• SET DEFAULT : jika data pada tabel rujukan (tabel user) dihapus atau update, maka isian pada tabel yang merujuk (tabel post) di-set menjadi nilai defaultnya (jika ada)
• CASCADE : jika data pada tabel rujukan (tabel user) dihapus atau update, maka isian pada tabel yang merujuk (tabel post) akan ikut dihapus atau di-update. Misalkan user A memiliki 10 isian post pada tabel post, kemudian user A tersebut dihapus dari tabel user, maka semua post yang berkaitan dengan user A juga akan dihapus dari tabel post. Dalam kasus update, maka isian kolom post_author akan ter-update sesuai update pada user_id di tabel user.
• RESTRICT : hampir mirip dengan NO ACTION dimana proses delete atau update tidak dapat dilakukan pada tabel yang jadi rujukan (tabel user).

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.

Selanjutnya untuk tabel comment memiliki ketentuan berikut:

• Kolom comment_id merupakan PRIMARY KEY dan AUTOINCREMENT.
• Kolom post_id tidak boleh kosong dan merujuk pada kolom post_id pada tabel post, menunjukkan post yang diberikan komentar.
• Kolom commentor_id tidak boleh kosong dan berisi user_id dari user yang memberikan komentar dan merujuk pada kolom user_id pada tabel user.
• Kolom comment_content berisi isian komentar (minimal panjang komentar 10 karakter).

Query berikut merupakan implementasi dari kriteria untuk tabel comment.

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.

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.

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