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

Question

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        |12345       |
|3          |1        |12345       |
|1          |2        |12345       |
|1          |1        |54321       |
|1          |2        |54321       |
|2          |2        |54321       |
|2          |2        |12345       |
|----------------------------------|

Can this be done easily? I also know that this can be accomplished with one or two triggers but I understood that to not handle deletions and the like. Is there another way to do this that is easier to implement? Also, data being written and read from this database is handled by my LabVIEW program.

Answer 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
...

Answer 2

Thank you Joel, Greenstone, Stoleg. After more research y'all's comments and answers have helped me figure out just what I needed. Turns out I was over thinking it when it comes to the triggers and the data integrity.

Recap of what was needed:

Patient table: Nothing

Test Table: For each unique foreign key (Patient ID), the Test ID would start over at 1

Sample Table: For each unique composite foreign key (Test ID AND Patient ID), the Sample ID would start over at 1.

• 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.

Answer:

This is very simple to implement with a trigger and maintain data integrity. Like Joel says, the data is a very direct structure where there can be many samples to one test, many tests to one patient, and only unique patients in the patient table. This allows for the use of cascading to prevent orphans and maintain data integrity. Setting up cascading satisfies all of required bullets. Example by Joel:

...
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
...

The next part was to have the auto-increment (this is not the built-in auto-inc) reset for each unique key or composite key. This can be accomplished by the use of BEFORE INSERT trigger. (Credit goes to Devart)

This is my modified trigger for my test table:

delimiter $$
CREATE TRIGGER `insert_test_auto_inc`
  BEFORE INSERT
  ON `tests`
  FOR EACH ROW

BEGIN
  SELECT COALESCE(MAX(`Test ID`) + 1, 1) INTO @`Test ID` FROM tests WHERE `Patient ID` = NEW.`Patient ID`;
  SET NEW.`Test ID` = @`Test ID`;
END
$$

How the trigger code works, starting with the MAX function. The Max of Null is Null and Null + 1 is Null. The COALESCE function returns the first non Null value, so if Test ID is not defined then that is the 1 after the comma. If Test ID is a value, say 1, then it returns it added to one so 1 + 1 = 2. The INTO part puts that value into the user variable @Test ID. The max value is only found for the row that match the Patient ID. This auto-incremented (by code, not built in) value gets written in to the NEW value of Test ID.

This is my modified trigger for my sample table:

delimiter $$
CREATE TRIGGER `insert_sample_auto_inc`
  BEFORE INSERT
  ON `samples`
  FOR EACH ROW

BEGIN
  SELECT COALESCE(MAX(`Sample ID`) + 1, 1) INTO @`Sample ID` FROM samples WHERE `Patient ID` = NEW.`Patient ID` AND `Test ID` = NEW.`Test ID`;
  SET NEW.`Sample ID` = @`Sample ID`;
END
$$

The same thing happens in the sample trigger except it now matches both Patient ID and Test ID using the WHERE statement.

Below are all of the required commands to create this sample database that works just like this question wants. This code was generated using the MySQL Workbench.

    delimiter $$

CREATE DATABASE `testdb` /*!40100 DEFAULT CHARACTER SET utf8 */$$

delimiter $$

CREATE TABLE `patients` (
  `Patient ID` int(10) unsigned NOT NULL,
  `First Name` varchar(45) DEFAULT NULL,
  `Last Name` varchar(45) DEFAULT NULL,
  `DOB` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`Patient ID`),
  UNIQUE KEY `PatientID_UNIQUE` (`Patient ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$

delimiter $$

CREATE TABLE `tests` (
  `Test ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Patient ID` int(10) unsigned NOT NULL,
  PRIMARY KEY (`Test ID`,`Patient ID`),
  KEY `fk_tests_patient_id_idx` (`Patient ID`),
  CONSTRAINT `fk_tests_patient_id` FOREIGN KEY (`Patient ID`) REFERENCES `patients` (`Patient ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8$$

CREATE
DEFINER=`root`@`localhost`
TRIGGER `testdb`.`insert_test_auto_inc`
BEFORE INSERT ON `testdb`.`tests`
FOR EACH ROW
-- Edit trigger body code below this line. Do not edit lines above this one
BEGIN
  SELECT COALESCE(MAX(`Test ID`) + 1, 1) INTO @`Test ID` FROM tests WHERE `Patient ID` = NEW.`Patient ID`;
  SET NEW.`Test ID` = @`Test ID`;
END
$$
delimiter $$

CREATE TABLE `samples` (
  `Sample ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Test ID` int(10) unsigned NOT NULL,
  `Patient ID` int(10) unsigned NOT NULL,
  `Count Value` int(11) DEFAULT NULL,
  PRIMARY KEY (`Sample ID`,`Test ID`,`Patient ID`),
  KEY `fk_samples_test_id_idx` (`Test ID`),
  KEY `fk_samples_patient_id_idx` (`Patient ID`),
  CONSTRAINT `fk_samples_patient_id` FOREIGN KEY (`Patient ID`) REFERENCES `patients` (`Patient ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_samples_test_id` FOREIGN KEY (`Test ID`) REFERENCES `tests` (`Test ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8$$

CREATE
DEFINER=`root`@`localhost`
TRIGGER `testdb`.`insert_sample_auto_inc`
BEFORE INSERT ON `testdb`.`samples`
FOR EACH ROW
-- Edit trigger body code below this line. Do not edit lines above this one
BEGIN
  SELECT COALESCE(MAX(`Sample ID`) + 1, 1) INTO @`Sample ID` FROM samples WHERE `Patient ID` = NEW.`Patient ID` AND `Test ID` = NEW.`Test ID`;
  SET NEW.`Sample ID` = @`Sample ID`;
END
$$

delimiter ;