Kendala Integritas di SQL: Panduan dengan Contoh

Bayangkan basis data rumah sakit di mana alergi pasien tidak boleh dibiarkan kosong atau sistem keuangan di mana jumlah transaksi harus berupa angka positif. Dalam skenario ini dan banyak lainnya, kita mengandalkan kendala integritas untuk memastikan data tetap akurat, konsisten, dan andal. 

Dalam SQL, kendala integritas adalah aturan yang kita terapkan pada tabel basis data untuk menjaga kualitas data. Aturan ini membantu mencegah kesalahan, menegakkan aturan bisnis, dan memastikan data kita mencerminkan entitas dan relasi dunia nyata yang diwakilinya.

Dalam artikel ini, kita akan membahas jenis-jenis kendala integritas penting di SQL, dengan penjelasan yang jelas dan contoh praktis untuk menggambarkan penggunaannya di basis data PostgreSQL. Meskipun kita menggunakan sintaks PostgreSQL, konsepnya mudah diterapkan pada dialek SQL lainnya.

Jika Anda ingin mempelajari lebih lanjut tentang SQL, lihat daftar kursus SQL ini.

Apa Itu Kendala Integritas di SQL?

Pertimbangkan skenario di mana kita memiliki tabel yang menyimpan informasi pengguna untuk aplikasi web. Data tertentu, seperti usia pengguna, mungkin bersifat opsional karena tidak menghalangi pengguna mengakses aplikasi. Namun, memiliki kata sandi untuk setiap pengguna sangat penting untuk proses masuk. Untuk mengatasi hal ini, kita akan menerapkan kendala integritas pada kolom kata sandi di tabel pengguna untuk menjamin bahwa setiap entri di tabel ini menyertakan kata sandi.

Pada intinya, kendala integritas sangat penting untuk:

• Mencegah data hilang.
• Memastikan semua data mematuhi tipe dan rentang nilai yang diharapkan.
• Menjaga keterkaitan yang benar antar data di berbagai tabel.

Dalam artikel ini, kita akan mengeksplorasi kendala integritas penting berikut di SQL:

• PRIMARY KEY: Mengidentifikasi setiap rekaman dalam tabel secara unik.
• NOT NULL: Memastikan suatu kolom tidak dapat berisi nilai NULL.
• UNIQUE: Memastikan semua nilai dalam satu kolom atau sekelompok kolom bersifat unik.
• DEFAULT: Menyediakan nilai bawaan untuk kolom ketika tidak ditentukan.
• CHECK: Memastikan semua nilai dalam kolom memenuhi kondisi tertentu.
• FOREIGN KEY: Membangun relasi antar tabel dengan merujuk ke kunci utama di tabel lain.

Studi Kasus: Basis Data Universitas

Mari kita pertimbangkan basis data relasional untuk sebuah universitas. Basis data ini berisi tiga tabel: students, courses, dan enrollments.

Tabel Students

Tabel students menyimpan informasi semua mahasiswa universitas.

• student_id: Pengenal mahasiswa
• first_name: Nama depan mahasiswa.
• last_name: Nama belakang mahasiswa.
• email: Alamat email mahasiswa.
• major: Jurusan mahasiswa.
• enrollment_year: Tahun saat mahasiswa terdaftar.

Tabel Courses

Tabel courses menyimpan informasi mata kuliah yang tersedia di universitas.

• course_id: Pengenal mata kuliah.
• course_name: Nama mata kuliah.
• department: Departemen mata kuliah.

Tabel Enrollments

Tabel enrollments menyimpan informasi mahasiswa mana yang terdaftar pada mata kuliah mana.

• student_id: Pengenal mahasiswa yang terdaftar pada mata kuliah.
• course_id: Pengenal mata kuliah.
• year: Tahun pendaftaran.
• grade: Nilai mahasiswa pada mata kuliah ini.
• is_passing_grade: Boolean yang menunjukkan apakah nilai tersebut lulus.

Sepanjang artikel ini, kita akan menggunakan basis data contoh ini dan menunjukkan beberapa cara untuk menegakkan integritas data. Kita akan menggunakan sintaks PostgreSQL dalam kueri kita. Namun, konsepnya mudah diterapkan pada varian SQL lainnya.

Kendala PRIMARY KEY

Universitas bertujuan untuk mengidentifikasi setiap mahasiswa secara unik. Menggunakan nama depan dan belakang untuk tujuan ini tidak disarankan karena kemungkinan duplikasi nama di antara mahasiswa. Demikian juga, mengandalkan alamat email tidak ideal karena mahasiswa dapat mengganti emailnya.

Solusi umum untuk memastikan identifikasi yang unik adalah menetapkan pengenal unik untuk setiap mahasiswa, yang kita simpan di kolom student_id. Kita dapat menerapkan kendala PRIMARY KEY pada kolom student_id untuk menjamin bahwa setiap mahasiswa memiliki pengenal unik.

Kendala ini didefinisikan dalam perintah CREATE TABLE, setelah penentuan tipe data kolom:

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    email TEXT,
    major TEXT,
    enrollment_year INT
);

Kueri di atas membuat tabel mahasiswa dengan enam kolom yang telah disebutkan.

Kendala PRIMARY KEY memastikan bahwa:

1. Setiap mahasiswa memiliki student_id.
2. Setiap student_id bersifat unik.

Kendala PRIMARY KEY pada beberapa kolom

Dalam situasi tertentu, kita perlu menggunakan beberapa kolom untuk mengidentifikasi setiap baris secara unik. Ambil contoh tabel enrollments. Seorang mahasiswa dapat mengambil beberapa mata kuliah, menghasilkan beberapa baris yang berbagi student_id yang sama. Demikian pula, sebuah mata kuliah dapat memiliki banyak mahasiswa, sehingga terdapat banyak baris dengan course_id yang sama.

Karena tidak ada satu bidang pun yang dapat mengidentifikasi baris secara unik, setiap catatan pendaftaran ditentukan menggunakan kombinasi student_id, course_id, dan year.

Saat melibatkan beberapa kolom, kendala PRIMARY KEY ditentukan di akhir perintah CREATE TABLE.

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    year INT,
    grade INT,
    is_passing_grade BOOLEAN,
    PRIMARY KEY (student_id, course_id, year)
);

Kendala integritas setelah pembuatan tabel

Ada dua cara untuk menambahkan kendala integritas. Kita baru saja mempelajari cara melakukannya saat tabel dibuat.

Bayangkan tabel sudah ada, tetapi Anda lupa menentukan kendala PRIMARY KEY. Anda juga dapat mendefinisikannya setelah tabel dibuat menggunakan perintah ALTER TABLE, seperti ini:

ALTER TABLE enrollments
ADD CONSTRAINT enroll_pk 
PRIMARY KEY (student_id, course_id, year);

Dalam kueri ALTER TABLE, kita menamai kendalanya enroll_pk (singkatan dari enrollment primary key). Nama ini bisa berupa pengenal apa pun yang Anda pilih, tetapi disarankan memilih nama yang secara ringkas menyampaikan tujuan kendala tersebut.

Memberi nama kendala integritas merupakan praktik yang baik karena memberikan beberapa manfaat:

• Memudahkan referensi, terutama saat Anda perlu memodifikasi atau menghapus kendala di masa mendatang.
• Mendukung pengelolaan dan pengorganisasian kendala, khususnya dalam basis data dengan jumlah kendala yang besar.

Kendala NOT NULL

Universitas ingin memastikan bahwa nama dan email setiap mahasiswa tercatat di basis data. Mereka tidak ingin staf lupa memasukkan salah satu dari kolom tersebut secara tidak sengaja.

Untuk mengatasinya, kita dapat menggunakan kendala NOT NULL pada tiga kolom ini saat membuat tabel:

CREATE TABLE students (
    student_id INT,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    email TEXT NOT NULL,
    major TEXT,
    enrollment_year INT
);

Kueri di atas menggunakan kendala NOT NULL untuk memastikan bahwa kolom first_name, last_name, dan email tidak dapat memiliki nilai NULL (tidak terdefinisi).

Menambahkan kendala NOT NULL ke tabel yang sudah ada dapat dilakukan menggunakan perintah ALTER TABLE. Sintaks untuk menambahkan kendala NOT NULL adalah sebagai berikut:

ALTER TABLE students
ALTER COLUMN first_name SET NOT NULL,
ALTER COLUMN last_name SET NOT NULL,
ALTER COLUMN email SET NOT NULL;

Kendala UNIQUE

Alamat email pada dasarnya unik untuk setiap individu. Dalam situasi di mana kita yakin suatu kolom tidak boleh memiliki nilai duplikat, sebaiknya hal tersebut ditegakkan di tingkat basis data. Ini membantu mencegah kesalahan dan memastikan integritas data.

Penambahan kendala ini dilakukan dengan cara yang sama seperti lainnya.

CREATE TABLE students (
    ...
    email TEXT UNIQUE,
    ...
);

Namun, dalam kasus kita, kita juga ingin menerapkan kendala NOT NULL. Kita dapat menerapkan beberapa kendala pada satu kolom dengan memisahkannya menggunakan spasi: 

CREATE TABLE students (
    ...
    email TEXT UNIQUE NOT NULL,
    ...
);

Perhatikan bahwa urutannya tidak masalah, kita juga bisa menggunakan NOT NULL UNIQUE.

Menambahkan kendala UNIQUE ke tabel yang sudah ada dilakukan sebagai berikut:

ALTER TABLE students 
ADD CONSTRAINT unique_emails UNIQUE (email);

Kendala UNIQUE pada beberapa kolom

Misalkan kita ingin memastikan bahwa nama mata kuliah setiap departemen bersifat unik di tabel courses. Dalam hal ini, kolom course_name dan department secara bersama-sama harus unik.

Saat melibatkan beberapa kolom, kendala ditambahkan di akhir perintah CREATE TABLE:

CREATE TABLE courses (
    course_id INT,
    course_name TEXT,
    department TEXT,
    UNIQUE (course_name, department)
);

Sebagai alternatif, kita dapat menambahkan kendala dengan mengubah tabel yang sudah ada. Dalam hal ini, kita memberikan tupel dengan nama-nama kolom:

ALTER TABLE courses 
ADD CONSTRAINT unique_course_name_department 
UNIQUE (course_name, department);

Perbandingan kendala NOT NULL UNIQUE vs PRIMARY KEY 

Kita telah mempelajari bahwa kendala PRIMARY KEY menegakkan keunikan dan tidak adanya nilai yang hilang. Jadi Anda mungkin bertanya-tanya apa perbedaan antara:

course_id INT PRIMARY KEY

course_id INT UNIQUE NOT NULL

Perbedaan antara NOT NULL UNIQUE dan PRIMARY KEY pada sebuah tabel terletak pada tujuan dan penggunaannya. 

Meskipun keduanya menegakkan keunikan dan non-null pada kolom, sebuah tabel hanya dapat memiliki satu PRIMARY KEY, yang ditujukan untuk mengidentifikasi setiap rekaman dalam tabel secara unik.

Di sisi lain, kombinasi kendala NOT NULL UNIQUE dapat diterapkan pada kolom tambahan untuk menegakkan nilai unik di setiap baris, tanpa nilai null, guna menjaga integritas data untuk aturan bisnis tertentu. Sebuah tabel dapat memiliki sejumlah kendala NOT NULL UNIQUE.

Keberadaan keduanya memungkinkan fleksibilitas yang lebih besar dalam perancangan basis data, memungkinkan berbagai cara untuk menegakkan keunikan dan integritas data sekaligus membedakan antara pengenal utama suatu rekaman dan atribut penting lainnya yang juga unik di dalam tabel.

Kendala DEFAULT

Mahasiswa mungkin membutuhkan waktu setelah mendaftar untuk memilih jurusan. Universitas ingin nilai kolom major berupa string ’Undeclared’ bagi mahasiswa yang belum memilih jurusan.

Untuk melakukannya, kita dapat menetapkan nilai bawaan pada kolom ini menggunakan kendala DEFAULT. Kita dapat mengubah tabel students seperti ini:

ALTER TABLE students
ALTER COLUMN major SET DEFAULT 'Undeclared';

Jika, sebagai gantinya, kita ingin menetapkan kendala DEFAULT saat tabel dibuat, kita dapat mendeklarasikannya setelah tipe data kolom:

CREATE TABLE students (
    ...
    major TEXT DEFAULT 'Undeclared',
    ...
);

Kendala CHECK

Di universitas ini, nilai berkisar dari 0 hingga 100. Tanpa kendala apa pun, kolom grade dari tabel enrollment menerima nilai bilangan bulat apa pun. Kita dapat memperbaikinya dengan menggunakan kendala CHECK untuk menegakkan nilai berada di antara 0 dan 100.

ALTER TABLE enrollments
ADD CONSTRAINT grade_range CHECK (grade BETWEEN 0 AND 100);

Secara umum, kendala CHECK memungkinkan kita memvalidasi kondisi spesifik yang harus dipenuhi data. Ini penting untuk memastikan konsistensi dan integritas data.

Kendala CHECK dapat melibatkan lebih dari satu kolom. Mari gunakan untuk memastikan bahwa grade dan is_passing_grade memiliki nilai yang konsisten. Misalnya nilai dianggap lulus jika minimal 60. Maka kita dapat memastikan bahwa is_passing_grade bernilai TRUE jika dan hanya jika nilai minimal 60. Mari lakukan saat pembuatan tabel untuk menunjukkan bagaimana kendala CHECK dideklarasikan dalam perintah CREATE TABLE:

CREATE TABLE enrollments (
    ...
    grade INT,
    is_passing_grade BOOLEAN,
    CONSTRAINT grade_check CHECK (grade BETWEEN 0 AND 100),
    CONSTRAINT is_passing_grade CHECK (
        (grade >= 60 AND is_passing_grade = TRUE) OR
        (grade < 60 AND is_passing_grade = FALSE)
    )
);

Ada masalah dengan kendala di atas. Saat seorang mahasiswa mendaftar ke mata kuliah, mereka belum memiliki nilai. Jadi kita harus mengizinkan nilai NULL pada kolom grade. Saat melakukan itu, kita juga perlu memperbarui kendala nilai lulus agar bernilai NULL ketika nilai masih tidak terdefinisi. Berikut cara memperbarui kendala tersebut untuk memperhitungkan hal ini:

CREATE TABLE enrollments (
    ...    
    grade INT NULL DEFAULT NULL,
    is_passing_grade BOOLEAN NULL DEFAULT NULL,
    CONSTRAINT grade_check CHECK (grade BETWEEN 0 AND 100),
    CONSTRAINT is_passing_grade CHECK (
        (grade IS NULL AND is_passing_grade IS NULL) OR
        (grade >= 60 AND is_passing_grade = TRUE) OR
        (grade < 60 AND is_passing_grade = FALSE)
    ),
    ...
);

Perhatikan bahwa pada kolom grade dan is_passing_grade kita menambahkan NULL pada tipe data dan sebagai nilai bawaan. Tujuannya semata untuk meningkatkan keterbacaan.

Sekarang mari lihat kondisi apa saja yang dapat kita tegakkan dengan kendala CHECK.

Kondisi rentang

Kita dapat memastikan bahwa nilai dalam sebuah kolom berada dalam rentang tertentu.

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
CHECK (column_name BETWEEN min_value AND max_value);

Kondisi daftar

Kita dapat memvalidasi bahwa nilai sebuah kolom cocok dengan salah satu dalam daftar nilai tertentu.

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
CHECK (column_name IN ('Value1', 'Value2', 'Value3'));

Kondisi perbandingan

Kita dapat membandingkan nilai dalam kolom untuk memastikan nilainya memenuhi kondisi tertentu (lebih besar dari, lebih kecil dari, dll.).

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
CHECK (column_name > some_value);

Kondisi pencocokan pola

Kita dapat menggunakan pencocokan pola (misalnya dengan LIKE atau SIMILAR TO) untuk memvalidasi data teks.

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
CHECK (column_name LIKE 'pattern');

Kondisi logis

Kita dapat membolehkan beberapa kondisi menggunakan operator logis (AND, OR, NOT).

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
CHECK (condition1 AND condition2 OR condition3);

Kondisi gabungan

Menerapkan kondisi pada beberapa kolom.

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
CHECK (column1 + column2 < some_value);

Kendala FOREIGN KEY

Kendala kunci asing digunakan untuk menautkan kolom dari dua tabel, memastikan integritas referensial data. Intinya, kunci asing pada satu tabel menunjuk ke kunci utama pada tabel lain, yang menunjukkan bahwa baris pada kedua tabel tersebut saling terkait. Ini memastikan Anda tidak dapat memiliki baris pada sebuah tabel dengan kunci asing yang tidak sesuai dengan baris mana pun pada tabel terkait yang memiliki kunci utama.

Dalam contoh kita, setiap rekaman di tabel enrollments merujuk pada seorang mahasiswa dan mata kuliah melalui kolom student_id dan course_id. Tanpa kendala apa pun, tidak ada yang menjamin bahwa nilai pengenal ini di tabel enrollments sesuai dengan entri yang ada di tabel students dan courses.

Berikut cara memastikan hal ini saat membuat tabel enrollments:

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    ...
    FOREIGN KEY (student_id) REFERENCES Students(student_id),
    FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);

Perhatikan bahwa agar keduanya menjadi kunci asing, kolom yang dirujuk harus merupakan kunci utama di tabel students dan courses.

Seperti halnya kendala lain, kita juga dapat mendeklarasikannya setelah pembuatan tabel menggunakan perintah ALTER TABLE:

ALTER TABLE enrollments
ADD CONSTRAINT fk_student_id
FOREIGN KEY (student_id)
REFERENCES students(student_id);

ALTER TABLE enrollments
ADD CONSTRAINT fk_course_id
FOREIGN KEY (course_id)
REFERENCES courses(course_id);

Menggabungkan Semuanya

Sepanjang artikel ini, kami menampilkan beberapa kendala integritas dan cara menggunakannya untuk meningkatkan basis data universitas. Berikut versi final perintah CREATE TABLE, yang menggabungkan semua yang telah kita pelajari.

Pertama, kita akan mendefinisikan tabel students:

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    major TEXT DEFAULT 'Undeclared',
    enrollment_year INT,
    CONSTRAINT year_check CHECK (enrollment_year >= 1900),
    CHECK (major IN (
        'Undeclared',
        'Computer Science',
        'Mathematics',
        'Biology',
        'Physics',
        'Chemistry',
        'Biochemistry'
    ))
);

Selanjutnya, mari definisikan tabel courses:

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name TEXT NOT NULL,
    department TEXT NOT NULL,
    UNIQUE (course_name, department),
    CHECK (department IN (
        'Physics & Mathematics',
        'Sciences'
    ))
);

Terakhir, kita akan mendefinisikan tabel enrollments, yang membangun relasi antara mahasiswa dan mata kuliah:

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    year INT CHECK (year >= 1900),
    grade INT NULL DEFAULT NULL,
    is_passing_grade BOOLEAN NULL DEFAULT NULL,
    CONSTRAINT grade_check CHECK (grade BETWEEN 0 AND 100),
    CONSTRAINT is_passing_grade CHECK (
        (grade IS NULL AND is_passing_grade IS NULL) OR
        (grade >= 60 AND is_passing_grade = TRUE) OR
        (grade < 60 AND is_passing_grade = FALSE)
    ),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id),
    PRIMARY KEY (student_id, course_id, year)
);

Kami menambahkan beberapa kendala dalam contoh akhir ini. Sebuah universitas memiliki himpunan departemen dan jurusan yang sudah diketahui. Oleh karena itu, himpunan nilai yang dapat dimiliki kolom major dan department bersifat terbatas dan diketahui sebelumnya. Dalam situasi seperti ini, disarankan menggunakan kendala CHECK untuk memastikan bahwa kolom-kolom tersebut hanya dapat mengambil nilai dari himpunan nilai yang diketahui.

Kesimpulan

Dalam artikel ini, kita mengeksplorasi berbagai jenis kendala integritas di SQL dan cara mengimplementasikannya menggunakan PostgreSQL. Kita membahas kunci utama, kendala NOT NULL, kendala UNIQUE, kendala DEFAULT, kendala CHECK, dan kendala FOREIGN KEY, disertai contoh praktis untuk masing-masing.

Dengan memahami konsep-konsep ini, kita dapat memastikan ketepatan, konsistensi, dan keandalan data.

Jika Anda ingin mempelajari lebih lanjut tentang pengorganisasian data secara efisien, lihat kursus ini tentang Perancangan Basis Data.