چگونگی ایجاد Primary Key در دیتابیس

هر داستان بزرگی با بحران هویت آغاز می‌شود. به طور مثال:

من کی هستم؟ چه کاری باید انجام دهم؟

این مقاله را با چگونگی انتخاب کلید اصلی (Primary Key) در دیتابیس، یا همان بحران هویت، آغاز و با چند مثال از ایجاد کلید اصلی در دیتابیس به پایان می‌رسانیم.

چگونه کلید اصلی را انتخاب کنیم؟

شاید به این فکر کنید که تنها در زندگی است که دچار بحران هویت می‌شویم، اما این درست نیست. وقتی می‌خواهیم دیتابیسی را ایجاد کنیم، همه چیز یک جور بحران هویت است و آن تنها دلیلی است که ما به کلید اصلی نیاز داریم. (آن‌ها هدف را پیدا می‌کنند و به ما می‌گویند تا چگونه بقیه را پیدا کنیم.)

تصور کنید شما رئیس جمهور و یا هر شخص تصمیم گیرنده در امور یک کشور هستید و قصد دارید تا شهروندان خود را به صورت دیجیتال و با یک ویژگی یکتا از یکدیگر تشخیص دهید. پس شما یک دیتابیس با تمام ویژگی‌هایی که آن‌ها دارند ایجاد می‌کنید. به طور مثال دیتابیسی با فیلدهای Last Name، First Name و Passport Number در نظر می‌گیرید.

First Name
Last Name
Passport Number

از میان این ۳ فیلد، فیلد Passport Number را برای کلید اصلی و متمایز کردن افراد از یکدیگر انتخاب می‌کنید. شما می‌دانید تمام چیزی که نیاز دارید پاسپورت است که شامل آدرس و تمام چیز‌های ضروری دیگر است. همچنین Passport Number یکتا است، پس تا به اینجای کار فکر می‌کنید که همه چیز درست است و این سیستم و دیتابیس را بر اساس این کلید اصلی، یعنی Passport Number ایجاد می‌کنید.

بعد گذشت چند سال از طراحی سیستم و دیتابیس فعلی، با یک مشکل روبرو می‌شوید و آن هم این است که کشور شما دچار بحران در تشخیص هویت اشخاص می‌شود. منظور از بحران هویت در اینجا چیست؟ در واقع پاسپورت هر شخص بعد از گذشت مدت محدودی منقضی می‌شود. آن شخص تقاضای پاسپورت جدید می‌دهد. اما نکته اینجاست که به ازای هر پاسپورت جدید یک Passport Number جدید تولید می‌شود. در واقع کلید اصلی شخص، بعد از گذشت مدتی به طور مثال ۴ سال عوض می‌شود و در همین حین سایر سیستم‌های متصل به این دیتابیس از همان کلید اصلی قدیمی استفاده می‌کنند که بعد از تغییر Passport Number یا کلید اصلی، چنین شخصی با آن Passport Number وجود ندارد. در واقع از کلید اصلی و یا IDیی استفاده می‌کنند که به هیچ شخصی اشاره نمی‌کند. با همه این تفاسیر به این نکته پی می‌بریم که یکتا بودن مقدار یک فیلد کافی نیست. در واقع برای انتخاب کلید اصلی به فیلدی نیاز داریم که در طول استفاده از آن دیتابیس و جدول، مقدارش تغییر نکند. یعنی از تولد تا مرگ یک سیستم این مقدار ثابت باشد.

از طرف دیگر به این قضیه پی می‌برید که تعداد زیادی از شهروندان شما اصلا پاسپورت ندارند. در این حالت نمی‌توانید افرادی را وارد سیستم کنید در حالی که می‌خواهید کلید اصلی آن‌ها را برابر Null قرار دهید. حتی اگر هم وارد سیستم کنید چگونه می‌خواهید یک شخص با کلید اصلی Null را از سایرین تشخیص دهید؟ پس هر ردیف در جدول دیتابیس باید یک هویت و یا ID منحصر به فرد خودش را داشته باشد.

بعد از مواجه شدن با این دو مشکل، نیاز داریم که یک فیلد مشخص کننده و تعیین کننده هویت هر شخص را ایجاد کنیم که در وهله اول در طول زمان تغییر نکند و در وهله دوم هر فرد شامل آن خصوصیت باشد. به طور مثال در هند این قضیه با کارتی به نام Adhaar Card و در آمریکا با Social Security Number حل شده است. پس اگر حالا قصد ایجاد دیتابیس را دارید، می‌توانید از این‌ها برای کلید اصلی استفاده کنید.

گاهی اوقات شما کلیدی مانند دو مثال قبل ندارید و یا کشوری را در نظر بگیرید که هنوز چیزی به نام Social Security Number ندارند و در عین حال می‌خواهند به ازای هر شهروند یک رکورد در دیتابیس خود داشته باشند. در این وضعیت یا باید یک SSN جدید ایجاد کنند و یا از یک کلید جایگزین استفاده کنند. کلید جایگزین هیچ معنایی در دنیای واقعی ندارد و تنها عدد و مقداری در دیتابیس است. با توجه به این نکته جدول مثال اول‌مان به شکل زیر تغییر می‌کند:

userID
First Name
Last Name
Passport Number

Passport Numberها یکتا هستند و هرگاه بخواهید یک شخص را از سایر اشخاص تشخیص دهید، می‌توانید از Passport Number استفاده کنید. اما فیلد userID هیچ وقت عوض نخواهد شد و از طرف دیگر Passport Number تغییر خواهد کرد اما به هر حال همیشه یکتاست. در نتیجه همیشه به هر شخصی که پاسپورت داشته باشد یک Passport Number داده خواهد شد. userID جایگزینی برای SSN در این کشور فرضی ما است که برای اشخاص چنین چیزی وجود ندارد. 

اما حقیقت این است که Passport Numberی که تغییر نکند، می‌تواند کلید اصلی ما باشد. این تنها یک تشخیص منطقی است که باید روی آن فکر شود. مهم‌ترین نکته به هنگام انتخاب کلید اصلی این است که آیا به موضوع بحران هویت و یا تشخیص هر فرد از یکدیگر، فکر کرده‌ایم؟ آیا امکان‌پذیر است که شخصی در آینده‌ای چه دور چه نزدیک کلید اصلی خودش را تغییر دهد؟ آیا وضعیتی پیش می‌آید که شخصی کلید اصلی برای خود نداشته باشد؟ آیا حالتی اتفاق خواهد افتاد که ۲ یا تعداد بیشتری از اشخاص، کلید اصلی یکسان داشته باشند؟

در این مثال از مردم یک کشور استفاده کردیم زیرا بحث هویت و یا ID هر ردیف در دیتابیس ملموس‌تر می‌شد و این را هم می‌دانیم که هر شخص، باید یک هویت و ID داشته باشد که از اشخاص دیگر متفاوت باشد، اما این طرز فکر در مورد مردم یک کشور را به مدل خود برای دیتابیس سیستم‌تان مرتبط کنید. از تشابه موجود بین مدل خود و این مثال استفاده کنید تا بهتر بتوانید کلید اصلی را انتخاب کنید و به این نکته هم توجه کنید که در جداول دیتابیس، هر ردیف یک ‌ID یکتا نیاز دارد و این همان دلیلی است که به کلید اصلی برای هر جدول نیاز داریم.

این نکته را هم در خاطر داشته باشید که گاهی اوقات برای ساخت کلید اصلی می‌توانیم از مقدار چند فیلد استفاده کنیم که به این نوع کلید، کلید مرکب یا ترکیبی می‌گویند.

حالا زمان آن رسیده است که کلید اصلی را در مثال‌های واقعی تعیین کنیم. در اینجا نیاز است تنها دو کار انجام دهید:

1. کلید اصلی را تشخیص دهید.
2. یاد بگیرید که چگونه کلید اصلی را در دیتابیس تعریف کنید.

یک مثال واقعی!

بیایید در نظر بگیریم یک استارت‌آپ در حوزه حمل و نقل مانند Flexport دارید. یک سری بسته دارید که نیاز است آن‌ها را از جایی تحویل بگیرید و به جای دیگری توسط کشتی‌ها منتقل کنید. علاوه بر این، مشتری‌هایی دارید که چنین بسته‌هایی را سفارش می‌دهند.

پس به یک جدول برای مشتریان،‌ یک جدول برای بسته‌ها و در انتها یک جدول برای انتقال بسته‌ها که مشخص می‌کند هر بسته در هر لحظه کجاست و چه وضعیتی دارد، نیاز داریم. به فیلد‌هایی که برای هر جدول نیاز دارید فکر کنید و این که در هر جدول کدام فیلد باید کلید اصلی باشد. اگر شما به عنوان مهندس در Flexport باشید، به این سوال باید پاسخ دهید. هیچ اطلاعاتی ندارید و تمام چیزی که می‌دانید را از شواهد بدست آوردید. 

با توجه به اطلاعاتی که دریافت کردیم، جداولی با فیلدهای زیر در نظر دارم:

Customers: first_name, last_name, email, address (for deliveries to their location)
Packages: weight, content
Transportation: <package_primary_key>, Port, time

اما کلید اصلی را فراموش کردیم. قبل از اینکه ادامه را بخوانید چند لحظه برای کلید اصلی هر کدام از این جداول فکر کنید.

برای جدول Packages، می‌توانیم کلید جانشین PackageID را برای هر بسته انتخاب کنیم. من تلاش کردم که همه ویژگی‌های یک بسته، مانند: وزن، حجم،‌ضخامت و … را لیست کنم که می‌توان به آن‌ها یک بسته را توصیف کرد، اما انجام چنین کاری در عمل سخت است، زیرا مردم به چنین چیزهایی اهمیت نمی‌دهند،‌آن‌ها تنها می‌خواهند بسته‌شان از مبدا به مقصد برسد.

بنابراین این موارد باعث می‌شود تا برای هر ID بسته، یک عدد رندوم ایجاد کنیم. این همان چیزی هست که در سایر سرویس‌های حمل و نقل به عنوان ID و یا بارکد میبینید. در دنیای واقعی هیچ معنایی ندارند و تنها هدفشان این است که توسط آن بتوان وضعیت بسته را دنبال کرد.

برای جدول Customers، از کلید CustomerID استفاده می‌کنیم. اینجا هم انتخاب‌های زیادی داشتیم، ‌مثل استفاده از SSN مشتریان. اما شاید مشتریان ما نخواهند SSN خودشان با در اختیار ما بگذارند. پس خودمان یک کلید ایجاد می‌کنیم و لازم نیست مشتریان ما چیزی در مورد این کلید بدانند و آن‌ها را به طور مثال با CustomerNo.4567 و یا مشتری-4567 خطاب می‌کنیم.

برای جدول Transportation، کلید اصلی مرکب ایجاد می‌کنیم. به نحوی که از ProjectID و Port و Time بدست می‌آید و این نکته جالبی خواهد بود که در ادامه بررسی می‌کنیم. اما در این جدول هم می‌توانستیم مانند دو جدول قبلی کلیدی مثل TransportID ایجاد کنیم. اما اینجا جادوی index پنهان است. کلید اصلی به طور خودکار index می‌شود، این یعنی جست‌وجو میان کلید اصلی به مراتب بهتر و موثرتر است.

وقتی در این دیتابیس به دنبال رکوردی بگردید، بیشتر سوال‌ها این است که: “این بسته الان کجاست؟”. به عبارت دیگر PackageID را دارید، حالا Port و Time فعلی بسته را به من تحویل دهید. در این حالت اگر PackageID بخشی از کلید اصلی نبود ما به index بیشتری نیاز داشتیم.

تعریف جداول در MySQL:

CREATE TABLE customers
( customerID  INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  last_name   VARCHAR(30) NOT NULL,
  first_name  VARCHAR(25) NOT NULL,
  email		  VARCHAR(50) NOT NULL,
  address     VARCHAR(300)
);

CREATE TABLE packages
( packageID  INT(15) NOT NULL AUTO_INCREMENT,
  weight     DECIMAL (10, 2) NOT NULL,
  content    VARCHAR(50),
  CONSTRAINT packages_pk PRIMARY KEY (packageID)
);

CREATE TABLE transportation
( package 	INT(15) NOT NULL,
  port  	INT(15) NOT NULL,
  time	 	DATE NOT NULL,
  
  PRIMARY KEY (package, port, time),
  FOREIGN KEY package
  	REFERENCES packages(packageID)
	ON DELETE RESTRICT
);

تعریف جداول در PostgreSQL:

CREATE TABLE customers
( customerID  SERIAL NOT NULL PRIMARY KEY,
  last_name   VARCHAR(30) NOT NULL,
  first_name  VARCHAR(25) NOT NULL,
  address     TEXT,
  email		  VARCHAR(50) NOT NULL
);

CREATE TABLE packages
( packageID  SERIAL NOT NULL,
  weight     NUMERIC NOT NULL,
  content    TEXT,
  CONSTRAINT packages_pk PRIMARY KEY (packageID)
);

CREATE TABLE transportation
( package 	INTEGER NOT NULL,
  port  	INT(15) NOT NULL,
  time	 	DATE NOT NULL,
  
  PRIMARY KEY (package, port, time),
  
  FOREIGN KEY package
  	REFERENCES packages(packageID)
	ON DELETE RESTRICT
);

خیلی هم سخت نبود، نه؟ وقتی اصول را بلد باشید، می‌توانید آن‌ها تقریبا بر روی هر دیتابیسی پیاده کنید. تنها لازم است که بدانید دنبال چه چیزی هستید.

منبع: https://www.freecodecamp.org/news/primary-key-sql-tutorial-how-to-define-a-primary-key-in-a-database