menu.sql

来自「Java的框架」· SQL 代码 · 共 39 行

USE PRRM;

CREATE TABLE IF NOT EXISTS MENU_ITEM (
	ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	PARENT_ID INT NOT NULL,
	NAME VARCHAR(60),
  TITLE VARCHAR(100),
  DESCRIPTION VARCHAR(255),
  TARGET VARCHAR(10),
  PAGE VARCHAR(255),
	ORDERING INT NOT NULL
) ENGINE=INNODB
;


CREATE TABLE IF NOT EXISTS MENU_ITEM_ROLE (
    ID INT NOT NULL,
    ROLE_NAME VARCHAR(50) NOT NULL,
    PRIMARY KEY (ID, ROLE_NAME),
    INDEX (ID),
		FOREIGN KEY (ID) REFERENCES MENU_ITEM (ID) ON DELETE CASCADE,
		INDEX (ROLE_NAME),
		FOREIGN KEY (ROLE_NAME) REFERENCES ROLE (NAME) ON DELETE CASCADE
) 	ENGINE=INNODB
;


CREATE TABLE IF NOT EXISTS MENU_ITEM_MAPPING (
	ID INT NOT NULL,
	PARENT_ID INT NOT NULL,
	PRIMARY KEY (ID, PARENT_ID),
	INDEX (ID),
	FOREIGN KEY (ID) REFERENCES MENU_ITEM (ID) ON DELETE CASCADE
) ENGINE=INNODB
;