Key និង Relationship

មាតិកា

  1. ហេតុអ្វី Table ត្រូវការ Relationship
  2. Primary Key, Surrogate និង Natural
  3. Foreign Key និង Referential Integrity
  4. One-to-Many៖ Categories និង Products
  5. ការ Migrate products.category
  6. អ្វីកើតឡើងពេលអ្នកលុប៖ ON DELETE
  7. Orders៖ អតិថិជនមាន Order ច្រើន
  8. Many-to-Many៖ order_items
  9. One-to-One ដោយសង្ខេប
  10. រូបភាពទាំងមូល
  11. schema.sql និង seed.sql ចុងក្រោយ
  12. សាកល្បងដោយខ្លួនឯង

1. ហេតុអ្វី Table ត្រូវការ Relationship

ឥឡូវនេះ shopdb មាន table ពីរដែលមិនស្គាល់គ្នាទេ។ products ថែមទាំងរក្សាទុកប្រភេទរបស់វាជាអក្សរសេរី៖ 'Electronics', 'Books', 'Home'។ វាដំណើរការរហូតដល់ជីវិតពិតមកដល់។ នរណាម្នាក់វាយ 'Electronic' ឬ 'books' ហើយឥឡូវនេះមាន "ប្រភេទ" ប្រាំ ទោះបីអ្នកចង់បានបី។ ប្តូរឈ្មោះ Home ទៅ Home & Living ហើយអ្នកត្រូវ update រាល់ row ផលិតផលដែលនិយាយថា Home ដោយសង្ឃឹមថាមិនខកខានមួយ។

ហើយយើងមិនទាន់ចាប់ផ្តើម order ផង។ order ជារបស់អតិថិជនម្នាក់ ហើយមានផលិតផលច្រើន។ តើអ្នកនឹងរក្សាទុកវានៅទីណា? ការចម្លងឈ្មោះ និង email របស់អតិថិជនចូលក្នុងគ្រប់ row order គឺជារបៀបដែលទិន្នន័យមិនស៊ីសង្វាក់គ្នា។

គំនិត relational ពីជំពូកទី 1 គឺជាដំណោះស្រាយ៖ រក្សាទុកការពិតនីមួយៗ ម្តង ក្នុង table ផ្ទាល់ខ្លួន ហើយចង្អុលទៅវាពីកន្លែងដែលត្រូវការ។ "ឧបករណ៍ចង្អុល" គឺ key។ ជំពូកនេះនិយាយអំពី key និងទំនាក់ទំនងបីប្រភេទដែលវាបង្កើត។

2. Primary Key, Surrogate និង Natural

primary key (PK) គឺជា column ឬសំណុំ column ដែលកំណត់អត្តសញ្ញាណ row តែមួយយ៉ាងពិតប្រាកដ។ វាត្រូវតែ unique ហើយមិនអាចជា NULL។ អ្នកបានផ្តល់ customers និង products នូវ column id ជាមួយ GENERATED ALWAYS AS IDENTITY PRIMARY KEY រួចហើយ។ Postgres សាងសង់ index នៅពីក្រោយឆាកដើម្បីអនុវត្តវា ហើយវាជាអ្វីដែល table ផ្សេងទៀតនឹងចង្អុលទៅ។

តើតម្លៃរបស់ key មកពីណា? មានសាលាពីរ៖

  • natural key គឺជាតម្លៃក្នុងពិភពពិតដែល unique រួចហើយ៖ លេខអត្តសញ្ញាណជាតិ, ISBN របស់សៀវភៅ, កូដប្រទេសដូច KH។
  • surrogate key គឺជាលេខគ្មានន័យដែល database បង្កើតឡើង ដូច identity id របស់យើង។

natural key ស្តាប់ទៅទាក់ទាញ ប៉ុន្តែវាមានបញ្ហា។ តម្លៃ "unique" ក្នុងពិភពពិតផ្លាស់ប្តូរ ឬប្រែជាមិន unique។ មនុស្សប្តូរអាសយដ្ឋាន email និងលេខទូរស័ព្ទ។ SKU របស់ផលិតផលត្រូវបានចេញឡើងវិញ។ រដ្ឋាភិបាលដាក់លេខឡើងវិញ។ ប្រសិនបើ key ផ្លាស់ប្តូរ រាល់ row ដែលចង្អុលទៅវាក៏ត្រូវផ្លាស់ប្តូរដែរ។

surrogate key មិនដែលផ្លាស់ប្តូរ និងគ្មានអត្ថន័យ ដូច្នេះវាមានសុវត្ថិភាពក្នុងការចង្អុលទៅ។ ការអនុវត្តជាវិជ្ជាជីវៈធម្មតាគឺ៖ ប្រើ surrogate id ជា primary key ហើយអនុវត្តភាព unique តាមធម្មជាតិដោយឡែកជាមួយ UNIQUE constraint។ នោះជាអ្វីដែល customers ធ្វើយ៉ាងពិតប្រាកដ៖ id គឺជា primary key ហើយ email គឺ UNIQUE ដើម្បីកុំឱ្យគណនីពីរមានអាសយដ្ឋានដូចគ្នា។

ករណីលើកលែងគឺ table ដែលអត្តសញ្ញាណរបស់វា គឺ ការបញ្ចូលគ្នានៃ key ផ្សេងទៀត។ អ្នកនឹងឃើញវាក្នុងផ្នែកទី 8 ដែល order_items ប្រើ composite key ដែលបង្កើតពី column ពីរ។

3. Foreign Key និង Referential Integrity

foreign key (FK) គឺជា column ដែលតម្លៃរបស់វាត្រូវតែត្រូវនឹង primary key នៃ row ក្នុង table ផ្សេង។ វាជារបៀបរបស់ database ក្នុងការនិយាយថា "row នេះជារបស់ row នោះ"។

ការសន្យាដែលវាអនុវត្តហៅថា referential integrity៖ អ្នកមិនអាចមានការយោងទៅអ្វីមួយដែលមិនមានបានទេ។ គ្មានផលិតផលក្នុងប្រភេទដែលមិនមាន។ គ្មាន order ពីអតិថិជនដែលមិនធ្លាប់ត្រូវបានបង្កើត។ ក្នុងកម្មវិធី អ្នកត្រូវចងចាំដើម្បីពិនិត្យរឿងនេះគ្រប់ទីកន្លែង ប៉ុន្តែនៅទីនេះ database ពិនិត្យឱ្យអ្នកគ្រប់ insert, update និង delete។

វាក្យសម្ព័ន្ធគឺ REFERENCES other_table(column) ទាំងក្នុងជួរ (inline) លើ column ឬជា table constraint។ អ្នកនឹងឃើញទាំងពីរខាងក្រោម។ តោះប្រើវា។

4. One-to-Many៖ Categories និង Products

ប្រភេទ (category) មានផលិតផលច្រើន ហើយផលិតផលមានប្រភេទតែមួយ។ នេះជាទំនាក់ទំនង one-to-many ដែលជាប្រភេទទូទៅបំផុត។ ច្បាប់សម្រាប់សាងសង់វា៖ ដាក់ foreign key នៅផ្នែក "ច្រើន"។ row ផលិតផលនីមួយៗរក្សា id របស់ប្រភេទរបស់វា។

បង្កើត table categories មុន ព្រោះ table ដែលត្រូវបានចង្អុលទៅត្រូវតែមានមុន table ដែលចង្អុល៖

CREATE TABLE categories (
  id   integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL UNIQUE
);

INSERT INTO categories (name) VALUES
  ('Electronics'),
  ('Books'),
  ('Home');

CREATE TABLE
INSERT 0 3

id ត្រូវបានកំណត់តាមលំដាប់នេះ៖ Electronics គឺ 1, Books គឺ 2, Home គឺ 3។ យើងប្រើ INSERT ច្រើន row តែមួយ ដូច្នេះលំដាប់គឺដូចអ្វីដែលយើងបានសរសេរយ៉ាងពិតប្រាកដ។ ផ្នែកដែលនៅសល់នៃស៊េរីពឹងផ្អែកលើលេខទាំងនេះ។

5. ការ Migrate products.category

ឥឡូវនេះ products ត្រូវតែប្តូរពី column អក្សរទៅជាការយោង។ យើងមិនអាចគ្រាន់តែលុប category បានទេ ព្រោះទិន្នន័យក្នុងវានឹងបាត់បង់។ នេះគឺជា migration តូចមួយ៖ ផ្លាស់ប្តូររចនាសម្ព័ន្ធរបស់ table ដែលមានទិន្នន័យរួចហើយ ដោយមិនបាត់បង់អ្វីដែលមានក្នុងវា។ រូបមន្តសុវត្ថិភាពតែងតែដូចគ្នា៖ បន្ថែមថ្មី ចម្លងទិន្នន័យ ពិនិត្យវា ហើយលុបអ្វីដែលចាស់។

ជំហានទី 1៖ បន្ថែម column ថ្មី ជា foreign key។ ដំបូងវា nullable ព្រោះ row ដែលមានស្រាប់មិនទាន់មានតម្លៃ៖

ALTER TABLE products
  ADD COLUMN category_id integer REFERENCES categories(id);

ALTER TABLE

ជំហានទី 2៖ ចម្លងទិន្នន័យ ដោយផ្គូផ្គងអក្សរចាស់របស់ផលិតផលនីមួយៗជាមួយឈ្មោះប្រភេទ។ UPDATE ... FROM អនុញ្ញាតឱ្យអ្នក update table មួយដោយប្រើ table មួយទៀត៖

UPDATE products
SET category_id = categories.id
FROM categories
WHERE categories.name = products.category;

UPDATE 8

ផលិតផលទាំងប្រាំបីបានរកឃើញការផ្គូផ្គង។ នេះគឺជាការមើលជាមុនពិតប្រាកដនៃ join៖ សម្រាប់ row ផលិតផលនីមួយៗ រក row ប្រភេទដែលឈ្មោះស្មើគ្នា ហើយយក id របស់វា។ ជំពូកបន្ទាប់ធ្វើឱ្យ join ក្លាយជាឧបករណ៍សំខាន់របស់អ្នក។

ជំហានទី 3៖ ពិនិត្យមុនបំផ្លាញអ្វីមួយ។ ផលិតផលណាដែលនៅតែគ្មាន category_id មានន័យថាមានការវាយខុសដែលមិនត្រូវគ្នា៖

SELECT id, name, category FROM products WHERE category_id IS NULL;

 id | name | category
----+------+----------
(0 rows)

សូន្យ row មានន័យថាគ្រប់ផលិតផលត្រូវបាន migrate។ ប្រសិនបើអ្នករកឃើញ row នៅទីនេះ អ្នកនឹងជួសជុលវាជាមុន (នេះជាកន្លែងដែលអក្សរសេរីខាំ)។

ជំហានទី 4៖ លុប column ចាស់៖

ALTER TABLE products DROP COLUMN category;

ALTER TABLE

មើលលទ្ធផលដោយ \d products ក្នុង psql៖

                            Table "public.products"
   Column    |     Type      | Collation | Nullable |      Default
-------------+---------------+-----------+----------+--------------------
 id          | integer       |           | not null | generated always as identity
 name        | text          |           | not null |
 price       | numeric(10,2) |           | not null |
 stock       | integer       |           | not null | 0
 category_id | integer       |           |          |
Indexes:
    "products_pkey" PRIMARY KEY, btree (id)
Check constraints:
    "products_price_check" CHECK (price >= 0::numeric)
    "products_stock_check" CHECK (stock >= 0)
Foreign-key constraints:
    "products_category_id_fkey" FOREIGN KEY (category_id) REFERENCES categories(id)

(លទ្ធផលធម្មតា៖ ប្លង់ពិតប្រាកដអាស្រ័យលើទទឹង terminal របស់អ្នក។) database បានដាក់ឈ្មោះ foreign key ថា products_category_id_fkey តាមលំនាំ table_column_fkey របស់វា។ អ្នកនឹងឃើញឈ្មោះនោះក្នុងសារកំហុស។ យើងទុក category_id ជា nullable ដោយចេតនា៖ វាអនុញ្ញាតឱ្យមានផលិតផលដែលមិនទាន់ត្រូវបានចាត់ថ្នាក់។ ប្រសិនបើគ្រប់ផលិតផល ត្រូវតែ មានប្រភេទ សូមបន្ថែម NOT NULL។

ឥឡូវនេះមើល referential integrity ដំណើរការ។ សាកល្បងដាក់ផលិតផលក្នុងប្រភេទដែលមិនមាន៖

UPDATE products SET category_id = 99 WHERE id = 1;

ERROR:  insert or update on table "products" violates foreign key constraint "products_category_id_fkey"
DETAIL:  Key (category_id)=(99) is not present in table "categories".

ត្រូវបានបដិសេធ។ ទិន្នន័យមិនអាចចង្អុលទៅអ្វីទាំងអស់ទេ។ ឥឡូវនេះទិសដៅផ្ទុយ៖ សាកល្បងលុបប្រភេទដែលផលិតផលនៅតែប្រើ។

DELETE FROM categories WHERE id = 1;

ERROR:  update or delete on table "categories" violates foreign key constraint "products_category_id_fkey" on table "products"
DETAIL:  Key (id)=(1) is still referenced from table "products".

ក៏ត្រូវបានបដិសេធដែរ។ database នឹងមិនទុកកូនកំព្រាទេ។

6. អ្វីកើតឡើងពេលអ្នកលុប៖ ON DELETE

កំហុសចុងក្រោយនោះគឺជាឥរិយាបថ លំនាំដើម ប៉ុន្តែវាជាជម្រើស។ នៅពេល row ដែលត្រូវបានចង្អុលទៅត្រូវបានលុប foreign key អាចមានប្រតិកម្មបួនបែប។ អ្នកជ្រើសរើសមួយដោយ ON DELETE៖

ជម្រើសអ្វីកើតឡើងចំពោះ row ដែលយោង
NO ACTION (លំនាំដើម)ការលុបបរាជ័យដោយមានកំហុស (ពិនិត្យនៅចុងបញ្ចប់នៃសេចក្តីថ្លែង)
RESTRICTការលុបបរាជ័យដោយមានកំហុស ភ្លាមៗ
CASCADErow ដែលយោងក៏ត្រូវបានលុបដែរ
SET NULLcolumn FK ក្នុង row ដែលយោងត្រូវបានកំណត់ទៅ NULL

(ក៏មាន SET DEFAULT ដែរ ដែលកម្រប្រើ។) ក្នុងការអនុវត្ត NO ACTION និង RESTRICT ដំណើរការដូចគ្នាសម្រាប់ query ភាគច្រើន ដូច្នេះគិតពីពួកវាថា "បដិសេធ"។

មួយណាត្រឹមត្រូវ? សួរថាតើទំនាក់ទំនងមានន័យអ្វី៖

  • ការលុប ប្រភេទ ដែលនៅតែមានផលិតផល៖ បដិសេធ។ ផលិតផលមិនគួរបាត់ប្រភេទរបស់វាដោយស្ងាត់ៗទេ ហើយអ្នកមិនចង់លុបផលិតផលទេ។ រក្សាលំនាំដើម។ (ប្រសិនបើ "មិនបានចាត់ថ្នាក់" អាចទទួលយកបាន SET NULL គឺជាជម្រើសសមហេតុផល។)
  • ការលុប អតិថិជន ដែលមាន order៖ បដិសេធ។ ប្រវត្តិ order សំខាន់សម្រាប់គណនេយ្យ។
  • ការលុប order៖ line item របស់វាគ្មានអត្ថន័យបើគ្មានវា ដូច្នេះវាគួរតែទៅជាមួយ។ CASCADE។

CASCADE ងាយស្រួល និងគ្រោះថ្នាក់ស្មើគ្នា ព្រោះ DELETE តែមួយអាចលុបដើមឈើទាំងមូលនៃ row។ ប្រើវាតែកន្លែងដែល row កូនពិតជាជារបស់ row មេ ដូចជា order line។

7. Orders៖ អតិថិជនមាន Order ច្រើន

អតិថិជនម្នាក់ធ្វើ order ច្រើន ហើយ order នីមួយៗជារបស់អតិថិជនម្នាក់៖ ទំនាក់ទំនង one-to-many មួយទៀត។ foreign key ទៅលើ orders៖

CREATE TABLE orders (
  id          integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id integer NOT NULL REFERENCES customers(id),
  status      text NOT NULL DEFAULT 'pending'
              CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled')),
  ordered_at  timestamptz NOT NULL DEFAULT now()
);

ប្រៀបធៀបជាមួយ foreign key មុន៖ មួយនេះគឺ NOT NULL។ រាល់ order ត្រូវតែមានអតិថិជន។ order ដែលមាន customer_id ជា NULL នឹងគ្មានន័យ ដូច្នេះយើងហាមឃាត់វា។ CHECK កំណត់ status ត្រឹមតម្លៃដែលស្គាល់បួន ជាលំនាំសម្រាប់សំណុំថេរតូចៗ។

សូមកត់សម្គាល់អ្វីដែល orders មិនមាន៖ ឈ្មោះ email ឬ city របស់អតិថិជន។ ពួកវារស់នៅក្នុង customers ម្តង ហើយ order រក្សាទុកតែ customer_id។ ផ្លាស់ប្តូរ city របស់អតិថិជន ហើយរាល់ order របស់គាត់ "ឃើញ" ការផ្លាស់ប្តូរដោយស្វ័យប្រវត្តិ ព្រោះមានកន្លែងតែមួយក្នុងការ update។

8. Many-to-Many៖ order_items

នេះជាសំណួរពិបាកជាង។ order អាចមានផលិតផលច្រើន ហើយផលិតផលអាចនៅក្នុង order ច្រើន។ នោះជា many-to-many។ តើ foreign key ទៅកន្លែងណា?

ទាំងសងខាងមិនដំណើរការទេ។ ប្រសិនបើអ្នកដាក់ product_id លើ orders order អាចមានផលិតផលតែមួយ។ ប្រសិនបើអ្នកដាក់ order_id លើ products ផលិតផលនីមួយៗអាចនៅក្នុង order តែមួយប៉ុណ្ណោះ រហូតទៅ។ database relational គ្មានវិធីរក្សាទុក "បញ្ជី id" ក្នុង column តែមួយតាមរបៀបត្រឹមត្រូវទេ។

ដំណោះស្រាយគឺ table ទីបី ហៅថា junction table (ឬ link, bridge ឬ associative table) ដែល row នីមួយៗជាការបំពេញគូមួយ៖ "order នេះមានផលិតផលនេះ"។ ផ្នែកនីមួយៗនៃ many-to-many ក្លាយជា one-to-many ជាមួយ junction table។

CREATE TABLE order_items (
  order_id   integer REFERENCES orders(id) ON DELETE CASCADE,
  product_id integer REFERENCES products(id),
  quantity   integer NOT NULL CHECK (quantity > 0),
  unit_price numeric(10,2) NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

មានបីរឿងដែលត្រូវអានដោយប្រុងប្រយ័ត្ននៅទីនេះ។

composite primary key។ PRIMARY KEY (order_id, product_id) មានន័យថា គូ កំណត់អត្តសញ្ញាណ row។ ផលិតផលដូចគ្នាមិនអាចត្រូវបានចុះបញ្ជីពីរដងក្នុង order ដូចគ្នាទេ ទោះបីវាអាចលេចឡើងក្នុង order ផ្សេងៗជាច្រើន ហើយ order អាចមានផលិតផលផ្សេងៗច្រើនក៏ដោយ។ ការទិញផលិតផលដូចគ្នាពីរដងក្នុង order តែមួយក្លាយជា row មួយដែលមាន quantity 2 មិនមែនពីរ row ទេ។ អ្នកនឹងឃើញ database អនុវត្តវាក្នុងផ្នែកទី 11 នៅពេលមានទិន្នន័យដើម្បីប៉ះទង្គិច។

ឥរិយាបថ ON DELETE ពីរផ្សេងគ្នា។ order_id cascade៖ លុប order ហើយ line របស់វាបាត់ទៅជាមួយ។ product_id ប្រើលំនាំដើម៖ អ្នកមិនអាចលុបផលិតផលដែលលេចឡើងក្នុង order ចាស់បានទេ ដែលការពារប្រវត្តិ។

unit_price ដែលបានចម្លង។ ផលិតផលមាន price រួចហើយ ដូច្នេះហេតុអ្វីរក្សាទុកម្តងទៀត? ព្រោះតម្លៃផ្លាស់ប្តូរ។ ប្រសិនបើ mouse មានតម្លៃ 15.00 ថ្ងៃនេះ និង 18.00 នៅឆ្នាំក្រោយ order កាលពីខែមករាត្រូវតែនៅតែនិយាយ 15.00 ឬមិនដូច្នោះទេ តួលេខប្រាក់ចំណូលអតីតកាលរបស់អ្នកនឹងផ្លាស់ប្តូរដោយខ្លួនឯង។ នេះជា snapshot ដោយចេតនា។ ជំពូកទី 10 ត្រឡប់មកវាវិញជាឧទាហរណ៍នៃពេលដែលការចម្លងទិន្នន័យជាការរចនាត្រឹមត្រូវ។

គុណលក្ខណៈណាដែលជារបស់ ទំនាក់ទំនង មិនមែនរបស់ផ្នែកណាមួយទេ (quantity និង unit_price នៅទីនេះ) រស់នៅក្នុង junction table។

9. One-to-One ដោយសង្ខេប

ប្រភេទទីបីគឺ one-to-one៖ row នីមួយៗក្នុង table A ត្រូវនឹងយ៉ាងច្រើនបំផុតមួយ row ក្នុង table B។ វាកម្ររកឃើញ។ ការប្រើប្រាស់ធម្មតាគឺការបំបែកសំណុំ column ធំ ឬរសើប (ឧទាហរណ៍ customer_profiles ដែលមាន bio វែង និងរូបថត) ឬផ្នែកបន្ថែមស្រេចចិត្តដែល row ភាគច្រើនមិនត្រូវការ។

អ្នកសាងសង់វាដោយ foreign key ដែលក៏ជា UNIQUE ដែរ (ឬដែលជា primary key ខ្លួនឯង)៖

CREATE TABLE customer_profiles (
  customer_id integer PRIMARY KEY REFERENCES customers(id),
  bio         text
);

ដោយសារ customer_id ជា primary key គ្មានអតិថិជនណាអាចមាន row profile ពីរបានទេ។ យើងនឹងមិនបន្ថែម table នេះទៅ shopdb ទេ៖ វានៅទីនេះតែដើម្បីបង្ហាញលំនាំ។ ក្នុងការរចនាភាគច្រើន ប្រសិនបើ table ពីរគឺ one-to-one យ៉ាងតឹងរ៉ឹង សូមសួរថាតើវាគួរតែជា table តែមួយឬអត់។

10. រូបភាពទាំងមូល

នេះជា table ទាំងប្រាំ និងរបៀបដែលពួកវាភ្ជាប់គ្នា។ អាន 1 --- N ថា "មួយនៅខាងឆ្វេង ច្រើននៅខាងស្តាំ"៖

customers 1 --- N orders 1 --- N order_items N --- 1 products
                                                       N
                                                       |
                                                       1
                                                  categories

ឬចុះបញ្ជី table នីមួយៗជាមួយ key របស់វា៖

customers    (id PK, name, email UNIQUE, city, created_at)
categories   (id PK, name UNIQUE)
products     (id PK, name, price, stock, category_id FK -> categories)
orders       (id PK, customer_id FK -> customers, status, ordered_at)
order_items  (order_id FK -> orders, product_id FK -> products,
              quantity, unit_price, PRIMARY KEY (order_id, product_id))

រូបរាងនេះជារូបរាងទូទៅ៖ customers ទៅ orders ទៅ order items គឺជាច្រវាក់នៃ one-to-many ហើយ order_items គឺជាស្ពានដែលធ្វើឱ្យ order និងផលិតផលជា many-to-many។ ពេលអ្នកអាចគូរទិន្នន័យរបស់អ្នកបែបនេះ អ្នកយល់ database របស់អ្នកហើយ។ ការគូរវាមុនសរសេរ CREATE TABLE គឺជាការអនុវត្តល្អ។

11. schema.sql និង seed.sql ចុងក្រោយ

ការវាយ migration នីមួយៗដោយដៃបានបង្រៀនអ្នកអំពីជំហាន ប៉ុន្តែឯកសារគម្រោងគួរពិពណ៌នាការរចនា ចុងក្រោយ ដោយផ្ទាល់ ដូច្នេះ database ថ្មីមិនត្រូវការ migration ទាល់តែសោះ។ ជំនួស shopdb/schema.sql ដោយកំណែពេញលេញ។ វាចាប់ផ្តើមដោយការលុប table ប្រសិនបើវាមាន តាមលំដាប់ដែលគោរព foreign key (កូនមុន) ដូច្នេះអ្នកអាចដំណើរការវាឡើងវិញបានគ្រប់ពេល៖

-- shopdb/schema.sql
DROP TABLE IF EXISTS order_items, orders, products, categories, customers
  CASCADE;

CREATE TABLE customers (
  id         integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name       text NOT NULL,
  email      text NOT NULL UNIQUE,
  city       text,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE categories (
  id   integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL UNIQUE
);

CREATE TABLE products (
  id          integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name        text NOT NULL,
  price       numeric(10,2) NOT NULL CHECK (price >= 0),
  stock       integer NOT NULL DEFAULT 0 CHECK (stock >= 0),
  category_id integer REFERENCES categories(id)
);

CREATE TABLE orders (
  id          integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id integer NOT NULL REFERENCES customers(id),
  status      text NOT NULL DEFAULT 'pending'
              CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled')),
  ordered_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
  order_id   integer REFERENCES orders(id) ON DELETE CASCADE,
  product_id integer REFERENCES products(id),
  quantity   integer NOT NULL CHECK (quantity > 0),
  unit_price numeric(10,2) NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

ឥឡូវនេះ shopdb/seed.sql។ វា truncate គ្រប់ table បន្ទាប់មក insert តាមលំដាប់ពឹងផ្អែក៖ មេមុនកូន ដូច្នេះរាល់ foreign key ចង្អុលទៅ row ដែលមានរួចហើយ។

-- shopdb/seed.sql
TRUNCATE order_items, orders, products, categories, customers
  RESTART IDENTITY CASCADE;

INSERT INTO customers (name, email, city) VALUES
  ('Sokha Chan', 'sokha@example.com', 'Phnom Penh'),
  ('Dara Kim',   'dara@example.com',  'Siem Reap'),
  ('Linda Vong', 'linda@example.com', 'Phnom Penh'),
  ('Vuthy Sok',  'vuthy@example.com', 'Battambang'),
  ('Maly Ros',   'maly@example.com',  NULL);

INSERT INTO categories (name) VALUES
  ('Electronics'), ('Books'), ('Home');

INSERT INTO products (name, price, stock, category_id) VALUES
  ('Wireless Mouse',      15.00,  40, 1),
  ('USB-C Cable',          5.50, 120, 1),
  ('Mechanical Keyboard', 79.00,  15, 1),
  ('SQL Cookbook',        32.00,  25, 2),
  ('Clean Code',          28.50,  30, 2),
  ('Desk Lamp',           22.00,   0, 3),
  ('Coffee Mug',           6.00,  80, 3),
  ('Notebook Pack',        9.50,  60, 2);

INSERT INTO orders (customer_id, status, ordered_at) VALUES
  (1, 'paid',      '2026-01-05'),
  (1, 'shipped',   '2026-01-20'),
  (2, 'pending',   '2026-02-03'),
  (3, 'paid',      '2026-02-10'),
  (2, 'cancelled', '2026-02-15');

INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
  (1, 1, 1, 15.00),
  (1, 2, 2,  5.50),
  (2, 3, 1, 79.00),
  (3, 4, 1, 32.00),
  (3, 5, 1, 28.50),
  (4, 1, 2, 15.00),
  (4, 7, 3,  6.00),
  (4, 8, 1,  9.50),
  (5, 6, 1, 22.00);

នៅទីនេះ id សំខាន់៖ order 1 ជារបស់អតិថិជន 1 (Sokha), order 3 និង 5 ជារបស់អតិថិជន 2 (Dara) ហើយ order 4 ជារបស់អតិថិជន 3 (Linda)។ Vuthy (អតិថិជន 4) និង Maly (អតិថិជន 5) គ្មាន order ដែលនឹងមានប្រយោជន៍ក្នុងជំពូកបន្ទាប់។

សាងសង់ database ទាំងមូលឡើងវិញពីសូន្យដោយ command ពីរ៖

docker exec -i shopdb-pg psql -U postgres -d shopdb \
  -v ON_ERROR_STOP=1 < schema.sql
docker exec -i shopdb-pg psql -U postgres -d shopdb \
  -v ON_ERROR_STOP=1 < seed.sql

ពិនិត្យ line item៖

SELECT * FROM order_items ORDER BY order_id, product_id;

 order_id | product_id | quantity | unit_price
----------+------------+----------+------------
        1 |          1 |        1 |      15.00
        1 |          2 |        2 |       5.50
        2 |          3 |        1 |      79.00
        3 |          4 |        1 |      32.00
        3 |          5 |        1 |      28.50
        4 |          1 |        2 |      15.00
        4 |          7 |        3 |       6.00
        4 |          8 |        1 |       9.50
        5 |          6 |        1 |      22.00
(9 rows)

នោះគឺប្រាំបួនបន្ទាត់ក្នុងប្រាំ order។ ឥឡូវនេះលេខមិនសូវអានបានទេ៖ product_id 7 មិនមានន័យអ្វីទេ រហូតដល់អ្នកមើលវាក្នុង products។ ការបម្លែង id ទាំងនោះត្រឡប់ទៅជាឈ្មោះវិញ ក្នុង query តែមួយ គឺជាការងាររបស់ JOIN។

composite key កំពុងដំណើរការ៖ ការបន្ថែម mouse របស់ order 1 ជាលើកទីពីរត្រូវបានបដិសេធ ព្រោះគូ (1, 1) មានរួចហើយ។

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (1, 1, 1, 15.00);

ERROR:  duplicate key value violates unique constraint "order_items_pkey"
DETAIL:  Key (order_id, product_id)=(1, 1) already exists.

ជាចុងក្រោយ រសជាតិនៃ CASCADE និងការការពារលើអតិថិជន។ អ្នកមិនទាន់បានរៀន transaction ទេ ដូច្នេះយើងរុំការពិសោធន៍ក្នុង BEGIN និង ROLLBACK ដែលបោះចោលការផ្លាស់ប្តូរ (ជំពូកទី 12 ពន្យល់ពីរបៀប)។ កុំរំលង ROLLBACK៖

BEGIN;
DELETE FROM orders WHERE id = 5;
SELECT count(*) FROM order_items WHERE order_id = 5;
ROLLBACK;

BEGIN
DELETE 1
 count
-------
     0
(1 row)

ROLLBACK

ការលុប order 5 បានលុប line item តែមួយរបស់វាដោយស្វ័យប្រវត្តិ។ បើគ្មាន rollback ទិន្នន័យនឹងបាត់ទៅ។ ហើយការព្យាយាមលុប Sokha ដែលមាន order៖

DELETE FROM customers WHERE id = 1;

ERROR:  update or delete on table "customers" violates foreign key constraint "orders_customer_id_fkey" on table "orders"
DETAIL:  Key (id)=(1) is still referenced from table "orders".

ប្រវត្តិត្រូវបានការពារ។ ទំនាក់ទំនងពីរផ្សេងគ្នា ច្បាប់ពីរផ្សេងគ្នា ទាំងពីរត្រូវបានអនុវត្តដោយ database។

12. សាកល្បងដោយខ្លួនឯង

  1. គូរវា។ លើក្រដាស គូរបណ្ណាល័យអនឡាញតូចមួយ៖ សៀវភៅ អ្នកនិពន្ធ សមាជិក និងការខ្ចី។ សៀវភៅអាចមានអ្នកនិពន្ធច្រើននាក់ អ្នកនិពន្ធអាចមានសៀវភៅច្រើន សមាជិកខ្ចីសៀវភៅច្រើនតាមពេលវេលា។ សម្គាល់ទំនាក់ទំនងនីមួយៗថា one-to-many ឬ many-to-many ហើយប្រាប់ថា foreign key នីមួយៗទៅកន្លែងណា។ តើ table ណាជា junction table?
  2. បំបែក foreign key។ បន្ទាប់ពី seed សូមដំណើរការនីមួយៗនេះលើ shopdb ហើយពន្យល់សារកំហុស៖ បញ្ចូល order សម្រាប់ customer_id 99; លុបផលិតផល 1; បញ្ចូល row order_items សម្រាប់ order 1 និងផលិតផល 99។
  3. ជ្រើសរើសច្បាប់។ សម្រាប់ foreign key នីមួយៗក្នុង shopdb សូមប្រាប់ថា NO ACTION, CASCADE ឬ SET NULL ជាឥរិយាបថ ON DELETE ដែលល្អបំផុត ហើយហេតុអ្វី។ តើចម្លើយរបស់អ្នកសម្រាប់ orders.customer_id នឹងផ្លាស់ប្តូរទេ ប្រសិនបើហាងត្រូវលុបទិន្នន័យអតិថិជនតាមសំណើ? (គិតអំពីអ្វីដែលអ្នកនឹងធ្វើជាមួយប្រវត្តិ order របស់ពួកគេ។)
  4. សរសេរ migration។ ក្នុង table សាកល្បង things(id, color text) ហាត់រូបមន្តបន្ថែម/ចម្លង/ពិនិត្យ/លុប៖ បង្កើត table colors បន្ថែម color_id ទៅ things បំពេញវាដោយ UPDATE ... FROM ពិនិត្យរក NULL ហើយលុប column អក្សរចាស់។ បន្ទាប់មក DROP TABLE ទាំងពីរនៅពេលអ្នករួចរាល់។
  5. Composite key។ សាកល្បងបញ្ចូល (order_id, product_id) ដូចគ្នាពីរដង។ បន្ទាប់មកផ្លាស់ប្តូរ quantity របស់ផលិតផលមួយលើ line ដែលមានស្រាប់ដោយប្រើ UPDATE ... WHERE order_id = ... AND product_id = ...។ ហេតុអ្វី WHERE ត្រូវការ column ទាំងពីរ?
  6. សាងសង់ឡើងវិញពីសូន្យ។ ដំណើរការ schema.sql ហើយបន្ទាប់មក seed.sql លើ database របស់អ្នក។ បញ្ជាក់ដោយ SELECT count(*) ថាអ្នកមាន 5 អតិថិជន, 3 ប្រភេទ, 8 ផលិតផល, 5 order និង 9 order item។ Commit ថត shopdb/ ទៅ git ប្រសិនបើអ្នកប្រើ version control។

ទៅទីណាបន្ត។ ទិន្នន័យឥឡូវនេះត្រូវបានបំបែកតាម table ដែលជាការរចនាត្រឹមត្រូវ ប៉ុន្តែមានន័យថាសំណួរមួយជារឿយៗត្រូវការ table ច្រើនក្នុងពេលតែមួយ៖ "តើ Sokha បានទិញអ្វី?", "តើអតិថិជនណាមិនធ្លាប់ធ្វើ order?"។ ការបញ្ចូល table គ្នាគឺជាបេះដូងនៃ SQL ហើយជំពូកបន្ទាប់បង្រៀនគ្រប់ប្រភេទនៃ join៖ Join។

មុនការ Query ដោយ SELECTបន្ទាប់JOIN

ទិញកាហ្វេជូនខ្ញុំ

ចូលចិត្តមេរៀននេះមែនទេ? សូមពិចារណាទិញកាហ្វេជូនខ្ញុំមួយកែវ ដើម្បីគាំទ្រការបង្កើតមាតិកាឥតគិតថ្លៃបន្ថែមទៀត!