MySQL – Data Integrity

MySQL තුළ දත්ත තළමනාකරණය කරන ක්‍රියාවලීන් මෙලෙස හඳුන්වයි. ඒවා ප්‍රධාන කොටස් 3 කි.
  1. Entity Integrity
  2. Referencial Integrity
  3. Domain Integrity
මෙහිදී මූලික වශයෙන් මුල් කරුණු 2 සලකා බලමු.

Entity Integrity

Table එකක් සැලකූ විට එහි ඇති records එකිනෙකින් වෙන් කර හඳුනා ගැනීමට හැකි විය යුතුය‍. එනම් එකම දත්තය පුනරාවර්ථ වීම වැලැක්විය යුතුය. ඒ සඳහා fields එකක් හෝ කිහිපයක් සලකා බලමින් එහි/ඒවායේ ඇති දත්ත එකම විටෙක නැවත යෙදීම වැලැක්විය යුතුය. එසේ විශේෂිත වූ fields, Primary Key ලෙස හඳුන්වයි.   උදා :
පාසලකට ළමයෙක් ඇතුලත් වන විට ඔහුට/ඇයට ඇතුලත්වීමේ අංකයක් හෝ ලියාපදිංචි වීමේ අංකයක් ලබා දේ. එය, ඔහුව/ඇයව ලේඛන වලදී අන් අයගෙන් වෙන් කර හඳුනා ගැනීමට භාවිතා කරයි. එනිසා එම ඇතුලත්වීමේ අංකය හෝ ලියාපදිංචි වීමේ අංකය ඇති තවත් අයෙක් සිසුන්ගේ විස්තර ඇති වගුවේ සිටිය නොහැකි බැවින් අළුතින් තවත් ළමයින් ඇතුලත් කිරීමේදී එම ඇතුලත්වීමේ අංකය හෝ ලියාපදිංචි වීමේ අංකය වෙනත් ළමයෙක්ට ලබා දිය නොහැක.
ඒ අනුව student table එකේ index_no යනු Primary Key එකයි.
පුස්ථකාලයක පොත් ගැනීමේදී අදාල පොත්වල සහ සාමාජිකයාගේ විස්තර ගබඩා කරගත යුතුය.ඒ සඳහා පහත ආකාරයක වගුවක් භාවිතා කරයි.
Borrowing
Book_No Member_No Date Time Due_Date Returned_Date Fine
B-001 M-001 2012-05-24 1:20:25 2012-05-31 2012-05-31 0.00
B-002 M-002 2012-05-24 1:25:30 2012-05-31 2012-05-25 0.00
B-002 M-001 2012-05-26 2:54:08 2012-06-02 2012-06-05 20.00
මෙහි Book_No පමණක් Primary Key විය නොහැක. එසේ වුවහොත් එකම Book_No එක නැවතත් මෙම වගුවේ ඇතුලත් විය නොහැකි නිසා එක් පොතක් ගැනිය හැක්කේ කවුරුන් හෝ එක් සාමාජිකයෙක්ට පමණි.
මෙහි Member_No පමණක් Primary Key වීමද සිදු විය නොහැක. එසේ වුවහොත් එකම Member_No එක නැවතත් මෙම වගුවේ ඇතුලත් විය නොහැකි නිසා එක් සාමාජිකයෙක්ට ගැනිය හැක්කේ කුමන හෝ හෝ එක් පොතක් පමණි.
ඊට අමතරව Book_No සහ Member_No යන දෙකම Primary Key කලහොත් යම් සාධාරණයක් සිදුවේ. නමුත් එවිට එකම Book_No එක, එකම Member_No එකක් සමඟ තවත් වතාවක් යෙදිය නොහැක. එනම් එක් සාමාජිකයෙකුට එකම පොත දෙවතාවක් ගැනිය නොහැක.
තවදුරටත් විමසා බැලූවිට, Book_No, Member_No සහ Date යන තුනම Primary Key කලහොත් වඩා නිවැරදි වේ. එවිට එකම සාමාජිකයාට එකම පොතක් එකම දිනයක් තුලදී දෙවතාවක් ලබා ගත නොහැක.
තවත් දුරට සලකා බැලූ විට, Book_No, Member_No, Date සහ Time යන හතරම Primary Key කලහොත් වඩාත්ම නිවැරදි වේ. එවිට එකම සාමාජිකයාට එකම පොතක් එකම දිනයක් තුලදී වුවද වෙනස් වෙලාවන් හිදී ලබා ගත හැක.
එනම් Book_No, Member_No, Date සහ Time යන හතරම Primary Key වේ. එසේ Fields එකකට වඩා වැඩියෙන් Primary Key ‍වේ නම්, එවැනි Primary Key, Composite Primary Key ලෙස හඳුන්වයි.

MySQL table එකක් තුලට Primary Key අන්තර්ගත කිරීම

පහත දක්වා ඇත්තේ පෙර ලිපියක සඳහන් කරන ලද studenttable එකට primary key එකක් ඇතුලත් කර ඇති ආකාරයයි.
	create table student( 		student_id varchar(10) not null, 		student_name varchar(50) not null, 		age int(2) default 5, 		primary key(student_id) 	);
පහත දක්වා ඇත්තේ මෙම ලිපියේ මුලින් සඳහන් කරන ලද borrowing table එකට primary key එකක් ඇතුලත් කර ඇති ආකාරයයි.
	create table borrowing( 		book_no varchar(10) not null, 		member_no varchar(10) not null, 		date date, 		time time, 		due_date date, 		returned_date date, 		fine double(7,2) default '0.00', 		primary key(book_no,member_no,date,time) 	);
මෙහිදී primary key ලෙස වරහන් තුල ඇති field එක හෝ fields භාවිතා කිරීමට සලස්වා ඇත.
Composite Primary Key බොහෝ විට ඇත්තේ එම fields වෙනත් tables වල field හා සම්බන්ධ වන විටදීය.

Referencial Integrity

Tables වලට වෙනත් tables වල ඇති fields අන්තර්ගත කිරීමෙන් එම tables අදාල table එක සමඟ සම්බන්ධ කලහැක. උදාරණ වශයෙන් ඉහත පුස්ථකාලයේ Borrowing table එක දැක්විය හැක. එහි Book_No සහ Member_No යනු වෙනත් tables වලින් ලැබෙන දත්ත සමඟ සම්බන්ධ වීමට භාවිතා කරන fields වේ.
එනම් Book_No යන field එක Book table එකෙන්ද, Member_No යන field එක Member table එකෙන්ද දත්ත ලබා ගනී. එහි අදහස වන්නේ, Borrowing table එකට දත්ත පේලියක්(record එකක්) ඇතුලත් කිරීමේදී එහි ඇති Book_No එක දැනට Book table එකේද, Member_No එක දැනට Member table එකේද තිබිය යුතුය. එසේ එක් table එකක ඇති දත්ත refer කිරීමට, එම table එකේ Primary key එක වෙනත් table එකක ඇතුලත් කලවිට එය Foreign Key ලෙස හඳුන්වයි. එසේ දත්ත සපයන table එක, Parent Table එක ලෙසත්, එම Primary Key එක භාවිතා කරමින් එම table එක සමඟ සම්බන්ධ වන table එක Child Table එක ලෙසත් හඳුන්වයි. එනම් Parent table එකේ Primary key එකක් හෝ කිහිපයක් Child table එකේදී Foreign keys ලෙස භාවිතා කලහැක. එය අවබෝධ කර ගැනීමට පහත රූප සටහන සලකා බලන්න.
(මෙහි යටින් ඇඳි ‍කඩ ඉරි වලින් Foreign Keys ද තද ඉරි වලින් Primary Keys ද නිරූපණය කර ඇත.)
මෙම රූප සටහනට අනුව Foreign Keys ඇතුලත් කල පසුව Borrowing table එකට අදාල MySQL code එක පහත දැක්වේ.
	create table borrowing( 		book_no varchar(10) not null, 		member_no varchar(10) not null, 		date date, 		time time, 		due_date date, 		returned_date date, 		fine double(7,2) default '0.00', 		primary key(book_no,member_no,date,time), 		foreign key(book_no) references book(book_no), 		foreign key(member_no) references book(member_no) 	);
පැහැදිලි කිරීම :
foreign key(book_no) references book(book_no) හි
references book(book_no) යන්නෙහි book යනු Parent table එක වන අතර වරහන් තුල ඇති book_no යනු එම Parent table එකේ refer වන field එක වේ.
foreign key(book_no) හි වරහන් තුල ඇති book_no යනු මෙම table එකේ Foreign Key ලෙස ක්‍රියා කරන field එකයි.
ඒ අනුව ඉහත රූප සටහනේ ඇති tables නිර්මාණය කිරීමට අදාල MySQL code එක පහත දැක්වේ.
	drop database if exists myDB; 	create database myDB; 	use myDB; 	create table member( 		member_no varchar(10) not null, 		member_name varchar(50) not null, 		contact_number varchar(11), 		address varchar(100), 		reg_date date, 		primary key(member_no) 	); 	create table book( 		book_no varchar(10) not null, 		book_name varchar(50) not null, 		book_description varchar(100), 		book_author varchar(50), 		primary key(book_no) 	); 	create table borrowing( 		book_no varchar(10) not null, 		member_no varchar(10) not null, 		date date, 		time time, 		due_date date, 		returned_date date, 		fine double(7,2) default '0.00', 		primary key(book_no,member_no,date,time), 		foreign key(book_no) references book(book_no), 		foreign key(member_no) references book(member_no) 	);
සැලකිය යුතුයි :
Foreign key යෙදීමේදී relationship එක සෑදෙන fields එකම data type එකෙන් තිබිය යුතුය. උදාහරණ ලෙස ඉහත member_no යන්න member table එකේ varchar(10) ලෙස ඇති නිසා එය refer කරන සියළුම child tables වල member_no යන field එක varchar(10) ලෙසම තිබිය යුතුය.
මෙසේ Referencial Integrity පවතින විටදී, parent table එකක් හෝ කිහිපයක් update/delete වන විටදී එය child tables වලට බලපායි.එය නිවැරදිව කළමනාකරණය කලයුතුය. උදා :
ඉහත උදාහරණයේ member table එකේ member කෙනෙක්ගේ member_no එකක් වෙනස් කිරීමට අවශ්‍ය යැයි සිතන්න.නමුත් දැනටමත් එම member_no එක borrowing table එකේ records වල ඇතුලත් වී තිබිය හැක. එවිට එම member_no එක member table එකේ පමණක් වෙනස් කල නොහැක. මක්නිසාද යත් එවිට Referencial Integrity යන සංකල්පය බිඳ වැටෙන නිසාය.(එසේ වෙනස් කිරීමට උත්සහ කලවිටද error එකක් ලැබේ.)
එ සඳහා නිවැරදි ලෙස tables define කල යුතුය. එනම්, parent table එකේ record එකක් update/delete කලවිට එය child tables වලට කෙසේ බලපාන්නේද යන්න define කලයුතුය. ඒ සඳහා child tables මෙසේ define කලහැක. parent table එක වෙනස් කිරීමේදී
  1. child table එකේ foreign key ඊට අනුව වෙනස් වේ.
  2. උදා :
ඉහත member table එකේ member_no එක වෙනස් කලවිට borrowing table එකේ අදාල member_no එක යෙදී ඇති instances සියල්ලම අනුරූපව වෙනස් කරයි.
  1. child table එකේ foreign key වශයෙන් එම records භාවිතා වී ඇත්නම් වෙනස් කිරීමට අවස්ථාව ලබා නොදීම.
  2. උදා :
ඉහත member table එකේ member_no එක වෙනස් කලවිට borrowing table එකේ අදාල member_no එක දැනටමත් යෙදී ඇති ඇත්නම් parent table එක වෙනස් කිරීමට අවස්ථාව ලබා නොදේ.ඒසේ නැත්නම් වෙනස් කලහැක.
  1. child table එකේ foreign key සඳහා වෙනත් value එකක් ලබාදිය හැක.
  2. උදා :
ඉහත member table එකේ member_no එක වෙනස් කලවිට borrowing table එකේ අදාල member_no එක යෙදී ඇති instances සියල්ලම දැනට සිටින වෙනත් member කෙනෙක්ගේ member_no එකක් හෝ වෙනත් දෙන ලද value එකක් බවට වෙනස් කරයි.
මෙසේ parent table එකේ records update කිරීමේදී සහ delete කිරීමේදී සිදු විය යුතු දේ define කිරීමට foreign key එක define කරන තැනදීම on update සහ on delete ලෙස අදාල option එක සඳහන් කලහැක. එසේ භාවිතා කලහැකි options ඉහත පැහැදිලි කරන ලදී.ඒවා MySQL වලදී මෙසේ භාවිතා වේ.
  1. cascade
ඉහත 1 හි සඳහන් කරන ලදී.
  1. restrict හෝ no action
ඉහත 2 හි සඳහන් කරන ලදී.
  1. set null හෝ set default
ඉහත 3 හි සඳහන් කල අයුරින් parent table එක වෙනස් වීමේදී child table එකේ foreign key එකට අදාල field එක null හෝ child table එකේ field definition එකේ default value එකක් සඳහන් කරන ලද නම් එය assign වේ.
update delete
cascade on update cascade on delete cascade
restrict on update restrict on delete restrict
no action on update no action on delete no action
set null on update set null on delete set null
set default on update set default on delete set default
ඉහත පැහැදිලි කිරීමට අනුව borrowing table එක වෙනස් කර ඇති අයුරු සලකා බලමු.
	create table borrowing( 		book_no varchar(10) not null, 		member_no varchar(10) not null, 		date date, 		time time, 		due_date date, 		returned_date date, 		fine double(7,2) default '0.00', 		primary key(book_no,member_no,date,time), 		foreign key(book_no) references book(book_no) on update cascade on delete cascade, 		foreign key(member_no) references member(member_no) on update cascade on delete restrict 	);
මෙහිදී තමාගේ අවශ්‍යතාවයට අනුව අදාල options එක භාවිතා කිරීමට වල බලා ගන්න.
{flike}

You may also like

MySQL – 04
MySQL – 02
MySQL – 01