alert.sql

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

USE PRRM;

CREATE TABLE IF NOT EXISTS ALERT (
    ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    SUBJECT VARCHAR(255) NOT NULL,
    ALERT_TEXT TEXT NOT NULL, 
		CREATOR VARCHAR(20) NOT NULL,
		CREATION_DATE DATETIME NOT NULL,
		STATUS VARCHAR(50) NOT NULL,
		ALERT_TYPE VARCHAR(20) NOT NULL 
) 	ENGINE=INNODB
;


CREATE TABLE IF NOT EXISTS ALERT_USER (
	ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	ALERT_ID INT NOT NULL,
	USERNAME VARCHAR(20) NOT NULL,
	SEND BIT,
	SEND_DATE DATETIME,
	ACKNOWLEDGE BIT,
	ACKNOWLEDGE_DATE DATETIME,
	ACKNOWLEDGE_TEXT TEXT,
	INDEX (USERNAME),
	FOREIGN KEY (USERNAME) REFERENCES USER (USERNAME) ON DELETE CASCADE,
	INDEX (ALERT_ID),
	FOREIGN KEY (ALERT_ID) REFERENCES ALERT (ID) ON DELETE CASCADE
) ENGINE=INNODB
;