انتقل إلى المحتوى
أمين أبوزيد
Englishالعربية
JNR-034

قيود قاعدة البيانات

  • 5 درس
  • 2 ساعة تدريب (تقدير)
ابدأ الوحدة
في هذه الصفحة 8 موضوعات

نص الدروس والتمارين باللغة الإنجليزية، بينما تبقى واجهة الموقع وعناوين الدروس بالعربية.

نظرة عامة

الموضوعات

  • الفريد (Unique)
  • المفاتيح الأجنبية
  • قيود التحقق (Check constraints)
  • مقدمة في الفهارس (Indexes)

تعلّم أولًا

2 ساعة تدريب (تقدير)

مخرجات التعلم

  1. Enforce invariants in the database with unique, foreign-key, not-null, and check constraints.
  2. Translate constraint violations into meaningful API errors (e.g., 409 for duplicates).
  3. Explain why application-level checks alone fail under concurrency.
  4. Create indexes for foreign keys and common lookups, and verify use with EXPLAIN.

الدروس

  1. 01قاعدة البيانات كخط الدفاع الأخير
  2. 02قيود Unique وCheck
  3. 03من انتهاكات القيود إلى أخطاء API
  4. 04الفهارس (Indexes): نظرة أولى
  5. 05عرض عملي: سباق بين طلبين على قيد Unique

المختبر والتمارين

Lab

JNR-034-LAB1 · Race the duplicate

Outcomes: 1–4 · Time: ~2 h

  1. Implement product creation with an application check only (existsBySku then save).
  2. Write a concurrent test: two threads released together create the same SKU; show it can create two rows.
  3. Add a unique constraint through a Flyway migration.
  4. Map the violation to a 409 response with a problem-details body.
  5. Add an index on order_line.order_id; show the plan with EXPLAIN.

Acceptance: the concurrent test reliably produces one success and one 409.

Exercises (assessment: add constraints and indexes to a schema from requirements)

IDOutcomeTask
JNR-034-EX011Add unique, foreign-key, not-null, and check constraints to a provided customer/orders schema.
JNR-034-EX021Write a composite unique constraint: one review per customer per product.
JNR-034-EX032Write an exception handler that maps a duplicate email to 409 and a check violation to 400.
JNR-034-EX043Explain with a timeline why existsBy then save can produce duplicates.
JNR-034-EX054Create indexes for foreign keys and a common lookup; explain why you did not index every column.
JNR-034-EX064Run EXPLAIN before and after an index and say what changed in the plan.
أظهر الحلول
  • EX01 unique (email), foreign key (customer_id) references customer(id), not null on required columns, check (total >= 0).
  • EX02 alter table review add constraint ux_review_customer_product unique (customer_id, product_id);
  • EX03 @ExceptionHandler(DataIntegrityViolationException.class) returning ProblemDetail with HttpStatus.CONFLICT for the unique constraint name and BAD_REQUEST for the check constraint name.
  • EX04 T1 checks: none. T2 checks: none. T1 inserts, T2 inserts. Both succeeded because neither saw the other's uncommitted insert.
  • EX05 Index foreign keys (order_line.order_id) and columns in frequent where and join; extra indexes slow writes and use space.
  • EX06 Before: Seq Scan on the table; after (on a large enough table): Index Scan using the new index, lower cost.

الاختبار

Outcomes: O1 constraints · O2 violations to API errors · O3 concurrency and application checks · O4 indexes and EXPLAIN.

اختبار ذاتي لك: لا توجد درجات، ولا يُحفظ أو يُرسل أي شيء. اختر إجابة ثم تحقق منها، ويمكنك المحاولة مرة أخرى كما تشاء.

  1. 1Which constraint prevents two rows with the same SKU?O1
  2. 2A check (price >= 0) constraint stops:O1
  3. 3A foreign key guarantees:O1
  4. 4A duplicate SKU should become HTTP:O2
  5. 5Spring translates a unique violation into:O2
  6. 6Responses should:O2
  7. 7"Check then insert" without a constraint fails because:O3
  8. 8The best protection against duplicates is:O3
  9. 9In EXPLAIN, a Seq Scan means:O4
  10. 10PostgreSQL automatically indexes:O4

الجزء العملي

Six exercises JNR-034-EX01 to EX06 plus the lab; the assessment is adding constraints and indexes to a schema from requirements (EX01, EX05). Pass: constraints and indexes correct, at least five of six exercises correct, and the concurrent test producing one success and one 409 every run.

أخطاء شائعة

"check then insert" without a constraint; indexing every column.

التقييم

Add constraints and indexes to a schema from requirements.

الصلة بالمسار الوظيفي

Index internals (MID-012) and data integrity (SRC-014).

اكتب للبحث في المستويات والمراحل والوحدات والدروس.