Integritas Basis Data Pada Mysql
Integritas
Basis Data Pada Mysql
1. Membuat Tabel dengan Key dan Foreign Key
Mendefinisikan Key dan Foreign Key pada dua tabel yang
berelasi dapat menjamin integritas basis data. Pada contoh ini akan dibuat dua
buah tabel yaitu tabel customer dan customer_sales. Struktur kedua tabel
tersebut adalah :
Tabel Customer
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| customer_id |
int(11) | | PRI | 0 |
|
| name | varchar(30) | YES | |
NULL | |
+-------------+-------------+------+-----+---------+-------+
Tabel Customer_sales
+----------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------+------+-----+---------+-------+
| transaction_id
| int(11) | | PRI | 0 |
|
| amount | int(11) | YES | |
NULL | |
|
customer_id | int(11) | | MUL | 0 |
|
+----------------+---------+------+-----+---------+-------+
Tabel Customer merupakan tabel induk dengan Primary Key
customer_id, sedangkan customer_id pada tabel customer_sales merupakan Foreign
Key yang mengacu pada tabel customer. Customer_sales kita sebut dengan tabel
anak. Untuk mendefinisikan kedua tabel tersebut perintah yang dipergunakan
adalah:
CREATE TABLE
customer
(
customer_id INT NOT NULL,
name
VARCHAR(30),
PRIMARY KEY (customer_id)
) TYPE = INNODB ;
CREATE TABLE
customer_sales
(
transaction_id INT NOT NULL,
amount INT,
customer_id INT NOT NULL,
PRIMARY KEY(transaction_id),
INDEX (customer_id),
FOREIGN KEY (customer_id) REFERENCES
customer (customer_id)
) TYPE = INNODB;
2. Insert Data pada tabel
Untuk melakukan Insert data pada kedua tabel
berikan perintah :
mysql>INSERT INTO customer
VALUES(1,'Desy'),(2,'Anton');
mysq>INSERT INTO customer_sales
VALUES(1,23,1),(3,81,2);
Insert
data pada customer_sales dengan data yang tidak ada pada tabel customer
mysql> INSERT
INTO customer_sales VALUES(2,39,3);
Insert
data customer_id 3 pada tabel customer_sales tidak diijinkan karena customer_id
tersebut tidak terdapat pada tabel induknya
(tabel customer).
4. Penghapusan Data.
Pada tabel
Parent tidak diijinkan penghapusan data customer_id apabila data dengan customer_id tersebut masih terdapat pada tabel child (customer_sales). Perintah yang diberikan adalah :
mysql> DELETE FROM customer WHERE
customer_id=3;
Hasil yang
akan diperoleh adalah :
ERROR 1217
(23000): Cannot delete or update a parent row: a foreign key constraint fails
Pengapusan
data pada Tabel Child (customer_sales)
mysql> delete
from customer_sales where customer_id=2;
Query OK, 1 row
affected (0.02 sec)
6. Drop
Foreign Key
Untuk
menghapus Foreign Key digunakan klausa drop tetapi tidak bisa dilakukan drop
secara langsung misalnya :
mysql> ALTER
TABLE customer_sales DROP FOREIGN KEY;
ERROR 1005
(HY000): Can't create table './latihan/#sql-671_9.frm' (errno: 150)
Untuk
melakukan Drop Foreign key diperlukan opsi foreign
key id yang digenerate pada saat foreign Key didefinisikan. Untuk
mengetahui Foreign key id digunakan perintah:
mysql> SHOW
CREATE TABLE customer_sales;
Hasilnya :
| Table | Create Table
|
customer_sales | CREATE TABLE `customer_sales`
(
`transaction_id` int(11) NOT NULL default
'0',
`amount` int(11) default NULL,
`customer_id` int(11) NOT NULL default '0',
PRIMARY KEY
(`transaction_id`),
KEY `customer_id` (`customer_id`),
CONSTRAINT `0_15` FOREIGN KEY (`customer_id`)
REFERENCES `customer` (`customer_id`)
) ENGINE=InnoDB
DEFAULT CHARSET=latin1 |
Constrain
‘0_15 merupakan foreign key id ,
nilai tersebut bisa berbeda pada implementasi yang lain .
Untuk
menghapus definisi foreign key dengan opsi tersebut perintahnya :
mysql> ALTER TABLE customer_sales DROP FOREIGN KEY
0_15;
Query OK, 1 row
affected (0.42 sec)
Setelah
dilakukan pengubahan pada definisi foreign key lakukan operas penghapusan pada
tabel customer dengan customer_id 1 dimana data dengan customer_id tersebut
masih tersimpan pada tabel customer_sales, perintah yang diberikan :
mysql> delete
from customer where customer_id=1;
Query OK, 1 row
affected (0.02 sec)
Lakukan
pula perintah untuk menambahkan data pada tabel customer_sales, dengan data
customer_id 7, dimana data tersebut tidak terdapat pada tabel customer.
mysql> insert
into customer_sales values('6','30','7');
Query OK, 1 row
affected (0.01 sec)
Penambahan
bisa dilakukan karena definisi Foreign Key telah dihilangkan.
7. Delete Foreign Key
Pada tabel
customer record data dapat dihapus dengan sekaligus menghapus data pada tabel
customer_sales dengan satu kali perintah delete. Proses delete semacam ini
disebut dengan cascade delete, dimana setiap data yang berelasi akan dihapus
Standard
pilihan pada saat dilakukan operasi DELETE adalah :
·
ON DELETE CASCADE
·
ON DELETE SET NULL
·
ON DELETE RESTRICT
·
ON DELETE NO ACTION
·
ON DELETE SET DEFAULT
- ON DELETE CASCADE
Buat
kembali table Customer dan customer_sales seperti di atas ( Drop terlebih
dahulu tabel jika masih ada atau bisa buat tabel dengan nama lain):
CREATE TABLE
customer
(
customer_id INT NOT NULL,
name
VARCHAR(30),
PRIMARY KEY (customer_id)
) TYPE = INNODB ;
CREATE TABLE
customer_sales
(
transaction_id INT NOT NULL,
amount INT,
customer_id INT NOT NULL,
PRIMARY KEY(transaction_id),
INDEX (customer_id),
FOREIGN KEY (customer_id) REFERENCES
customer (customer_id) ON DELETE CASCADE
) TYPE = INNODB;
Pada
contoh tersebut dipergunakan opsi ON DELETE CASCADE. Selanjutnya isikan data
pada kedua tabel tersebut seperti data diatas:
mysql> INSERT INTO
customer VALUES(1,'Desy'),(2,'Anton'),('3','Budi');
mysql> INSERT INTO
customer_sales VALUES(1,23,1),(3,81,2),('4','34','3');
Hasil dari
Insert data pada tabel tersebut adalah:
mysql> select
* from customer; select * from customer_sales;
+-------------+-------+
| customer_id |
name |
+-------------+-------+
| 1 | Desy |
| 2 | Anton |
| 3 | Budi |
+-------------+-------+
3 rows in set
(0.00 sec)
+----------------+--------+-------------+
| transaction_id
| amount | customer_id |
+----------------+--------+-------------+
| 1 | 23 | 1 |
| 3 | 81 | 2 |
| 4 | 34 | 3 |
+----------------+--------+-------------+
3 rows in set
(0.00 sec)
Lakukan
penghapusan record pada tabel customer dengan customer_id 3 yang akan
dijalarkan ke tabel customer_sales. Perintahnya adalah:
mysql> delete
from customer where customer_id=3;
Query OK, 1 row
affected (0.06 sec).
Setelah
penghapusan data tersebut maka isi record kedua tabel adalah :
mysql> select
* from customer; select * from customer_sales;
+-------------+-------+
| customer_id |
name |
+-------------+-------+
| 1 | Desy |
| 2 | Anton |
+-------------+-------+
2 rows in set
(0.00 sec)
+----------------+--------+-------------+
| transaction_id
| amount | customer_id |
+----------------+--------+-------------+
| 1 | 23 | 1 |
| 3 |
81 | 2 |
+----------------+--------+-------------+
2 rows in set
(0.00 sec)
Penghapusan
data customer_id 3 pada tabel customer akan dijalarkan pada tabel
customer_sales, dimana pada tabel customer_sales customer_id merupakan foreign
key.
- ON DELETE SET NULL
ON DELETE
SET NULL berbeda dengan ON DELETE CASCADE , On delete set NULL akan melakukan
seting pada foreign key menjadi bernilai NULL.
Contoh :
Buat dua
buah tabel yaitu tabel parent dan tabel child. Pada tabel child didefinisikan
foreign key dengan option ON DELETE SET NULL, perintahnya adalah :
CREATE TABLE
parent
(
par_id
INT NOT NULL,
PRIMARY KEY (par_id)
) TYPE = INNODB;
CREATE TABLE
child
(
par_id
INT NULL,
child_id
INT NOT NULL,
UNIQUE (par_id, child_id),
FOREIGN KEY (par_id) REFERENCES parent
(par_id) ON DELETE SET NULL
) TYPE = INNODB;
Masukkan Data pada tabel parent dengan
perintah:
mysql> INSERT
INTO parent (par_id) VALUES(1),(2),(3);
Query OK, 3 rows
affected (0.06 sec)
Data pada
tabel Parent adalah :
mysql> select * from parent;
+--------+
| par_id |
+--------+
| 1 |
| 2 |
| 3 |
+--------+
3 rows in set
(0.00 sec)
Masukkan
data pada tabel child dengan perintah
mysql> INSERT
INTO child (par_id,child_id) VALUES(1,1),(1,2);
Query OK, 2 rows
affected (0.02 sec)
mysql> INSERT
INTO child (par_id,child_id) VALUES(2,1),(2,2),(2,3)
mysql>INSERT
INTO child (par_id,child_id) VALUES(3,1);
Masukkan
data lain pada tabel child dengan perintah:
mysql> INSERT
INTO child (par_id,child_id) VALUES(4,1);
ERROR 1216
(23000): Cannot add or update a child row: a foreign key constraint fails
Insert
data pada perintah yang kedua tidak bisa dilaksanakan karena par_id 4 belum ada
pada tabel parent. Hal tersebut sama hasilnya dengan option ON DELETE CASCADE.
Untuk
melihat hasil SET NULL setelah penghapusan berikan perintah untuk menghapus
dari tabel parent dengan par_id 1 yaitu :
mysql> DELETE
FROM parent where par_id = 1;
Query OK, 1 row
affected (0.03 sec)
Hasil
setelah operasi penghapusan dapat
dilihat dengan perintah :
mysql> select
* from child;
+--------+----------+
| par_id |
child_id |
+--------+----------+
| NULL |
1 |
| NULL |
2 |
| 2 |
1 |
| 2 |
2 |
| 2 |
3 |
| 3 |
1 |
+--------+----------+
6 rows in set
(0.00 sec)
- ON DELETE RESTRICT dan ON DELETE NO ACTION
Kedua opdi
tersebut sama dengan contoh pertama, tidak mengijinkan penghapusan pada tabel
parent apabila masih ada record yang berasosiasi pada tabel child. Penggunaan
opsi ini sama dengan pendefinisian tanpa opsi. Buat contoh dengan tabel dan isi
record menggunakan opsi ini.
- ON DELETE SET DEFAULT
Opsi ini
dipergunakan apabila foreign key didefinisikan dengan suatu nilai default.
Record foreign key yang dihapus akan di ganti dengan nilai default. Buat contoh
dengan tabel dan isi record menggunakan opsi ini.
0 komentar: