Auto-increment key and Foreign Key as composite for relational database

I do not have any experience in databases other than what I have from just playing with workbench and trying to understand how I need to setup this database for a project. I have been searching the internet and I have become increasingly confused as to how I will do what I want to do. Below is what I am trying to accomplish.

I have a need for a MySQL database that will hold patient information. Patient information will be name, id (unique), and so on as well as all of the tests and samples for each test.

• Each patient can have multiple tests

• Each test can have multiple samples

• If I delete a patient then all test and samples are deleted

• If I delete a test then all samples will be deleted

• If I delete all samples of one test OR all tests of one patient then the test OR patient should not be deleted.

So far I understand a relational database is what I need. I also understand that I can use foreign keys to link the patient id's for each table as well as the test id for each table. I have also come to the conclusion that I would like data integrity so that I don't have to manually manage deleting and updating the rows. This would also not allow for orphaned rows. I believe this can be done with a composite key but this is where I start getting confused on how to set it up.

I would expect it to work like this (with each line being an insert statement):

Patient Table:

|-------------------|
|Patient ID  |Name  |
|-------------------|
|12345       |ANG   |
|54321       |JUE   |
|-------------------|

Tests Table:

|----------------------|
|Test ID  |Patient ID  |
|----------------------|
|1        |12345       |
|2        |12345       |
|3        |12345       |
|1        |54321       |
|2        |54321       |
|4        |12345       |
|----------------------|

Sample Table:

```
|----------------------------------|
|Sample ID |Test ID |Patient ID |
|----------------------------------|
|1 |1 |12345 |
|2 |1

You don't need composite keys to enforce your referential integrity in your case. The reason is that you have a pretty straight-forward three tier hierarchy:

PATIENT 
   +
   |
   ^
 TEST 
   +
   |
   ^
SAMPLE

Your SAMPLE table just needs a simple foreign key to your TEST table and your TEST table just needs a simple foreign key to your PATIENT table.

This works because each sample record needs a test record and each test needs a patient. If you delete a patient, their tests must be cascade deleted. If you delete a test its samples must be cascade deleted. Therefore you don't need patient_id in the TEST table. All you need is to declare cascade deletion on your foreign keys, like so...

...
CONSTRAINT 'FK_TEST__PATIENT' FOREIGN KEY ('patient_id')
      REFERENCES 'PATIENT' ('patient_id') ON DELETE CASCADE
...
CONSTRAINT 'FK_SAMPLE__TEST' FOREIGN KEY ('test_id')
      REFERENCES 'TEST' ('test_id') ON DELETE CASCADE
...

StackExchange Database Administrators Q#44407, answer score: 5