blob: 0d04f27f51f804af3446d3c7f4186caae8412d17 (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SET FOREIGN_KEY_CHECKS=0;
CREATE TABLE staff (
staff_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
store_id TINYINT UNSIGNED NOT NULL,
PRIMARY KEY (staff_id),
KEY idx_fk_store_id (store_id),
CONSTRAINT fk_staff_store FOREIGN KEY (store_id) REFERENCES store (store_id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB;
CREATE TABLE store (
store_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
manager_staff_id TINYINT UNSIGNED NOT NULL,
PRIMARY KEY (store_id),
UNIQUE KEY idx_unique_manager (manager_staff_id),
CONSTRAINT fk_store_staff FOREIGN KEY (manager_staff_id) REFERENCES staff (staff_id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB;
SET FOREIGN_KEY_CHECKS=DEFAULT;
LOCK TABLE staff WRITE;
UNLOCK TABLES;
DROP TABLES staff, store;
|
