MariaDB 10.5

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
SELECT VERSION();


VERSION()


10.5.17-MariaDB-1:10.5.17+maria~deb11


CREATE TABLE `materials` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `stock_activity_details` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `title` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `stock_activities` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `material_id` int UNSIGNED NOT NULL,
  `warehouse_id` int UNSIGNED NOT NULL,
  `detail_id` int UNSIGNED NOT NULL,
  CONSTRAINT `stock_activities_ibfk_1`
    FOREIGN KEY (`material_id`) REFERENCES `materials` (`id`),
  CONSTRAINT `stock_activities_ibfk_2`
    FOREIGN KEY (`detail_id`) REFERENCES `stock_activity_details` (`id`)
      ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `materials`
  (`id`, `name`)
VALUES
  (1, 'Foo'),
  (2, 'Bar');

INSERT INTO `stock_activity_details`
  (`id`, `title`)
VALUES
  (1, 'Lorem'),
  (2, 'ipsum');

INSERT INTO `stock_activities`
  (`material_id`, `detail_id`, `warehouse_id`)

Records: 2  Duplicates: 0  Warnings: 0



Records: 2  Duplicates: 0  Warnings: 0



Records: 2  Duplicates: 0  Warnings: 0


SELECT
  `id`,
  `name`
FROM
  `materials`;

SELECT
  `id`,
  `material_id`,
  `warehouse_id`,
  `detail_id`
FROM
  `stock_activities`;

SELECT
  `id`,
  `title`
FROM
  `stock_activity_details`;


id
name


1
Foo

2
Bar




id
material_id
warehouse_id
detail_id


1
1
8
1

2
2
9
2




id
title


1
Lorem

2
ipsum


DELETE FROM `materials`
WHERE `id` = 1;
SELECT
  `id`,
  `name`
FROM
  `materials`;

SELECT
  `id`,
  `material_id`,
  `warehouse_id`,
  `detail_id`
FROM
  `stock_activities`;

SELECT
  `id`,
  `title`
FROM
  `stock_activity_details`;


id
name


2
Bar




id
material_id
warehouse_id
detail_id


2
2
9
2




id
title


2
ipsum