CREATE TABLE project (
id INTEGER NOT NULL AUTO_INCREMENT,
created_at DATETIME NOT NULL,
name VARCHAR(75) NOT NULL,
description LONGTEXT NOT NULL,
is_active TINYINT NOT NULL DEFAULT '1',
PRIMARY KEY (id),
INDEX(name, created_at)
)
ENGINE = INNODB;
CREATE TABLE role (
id INTEGER NOT NULL,
name VARCHAR(50) NOT NULL,
description LONGTEXT NOT NULL,
PRIMARY KEY (id)
)
ENGINE = INNODB;
CREATE TABLE organization (
id INTEGER NOT NULL AUTO_INCREMENT,
created_at DATETIME NOT NULL,
name VARCHAR(100) NOT NULL,
is_active TINYINT NOT NULL DEFAULT '1',
PRIMARY KEY (id)
)
ENGINE = INNODB;
CREATE TABLE user (
id INTEGER NOT NULL AUTO_INCREMENT,
created_at DATETIME NOT NULL,
role_id INTEGER NOT NULL,
organization_id INTEGER NOT NULL,
last_login_at DATETIME NOT NULL,
last_ip_address VARCHAR(25) NOT NULL,
username VARCHAR(45) NOT NULL,
password CHAR(32) NOT NULL,
email_address VARCHAR(255) NOT NULL,
first_name VARCHAR(45) NOT NULL,
last_name VARCHAR(45) NOT NULL,
address_1 VARCHAR(100) NOT NULL,
address_2 VARCHAR(25) NULL,
city VARCHAR(25) NOT NULL,
state CHAR(2) NOT NULL,
zip_code VARCHAR(10) NOT NULL,
primary_phone_number VARCHAR(10) NOT NULL,
secondary_phone_number VARCHAR(10) NOT NULL,
is_primary_organization_contact TINYINT NOT NULL DEFAULT '0',
is_active TINYINT NOT NULL DEFAULT '1',
PRIMARY KEY (id),
CONSTRAINT fk_user_role_id
FOREIGN KEY (role_id)
REFERENCES role (id)
ON UPDATE RESTRICT
ON DELETE RESTRICT,
CONSTRAINT fk_user_organization_id
FOREIGN KEY (organization_id)
REFERENCES organization (id)
ON UPDATE RESTRICT
ON DELETE RESTRICT
)
ENGINE = INNODB;
CREATE TABLE project_user (
user_id INTEGER NOT NULL,
project_id INTEGER NOT NULL,
PRIMARY KEY (user_id, project_id),
CONSTRAINT fk_project_user_user_id
FOREIGN KEY (user_id)
REFERENCES user (id)
ON UPDATE RESTRICT
ON DELETE CASCADE,
CONSTRAINT fk_project_user_project_id
FOREIGN KEY (project_id)
REFERENCES project (id)
ON UPDATE RESTRICT
ON DELETE RESTRICT
)
ENGINE = INNODB;
CREATE TABLE ticket_category (
id INTEGER NOT NULL AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
description LONGTEXT NOT NULL,
PRIMARY KEY (id)
)
ENGINE = INNODB;
CREATE TABLE ticket_type (
id INTEGER NOT NULL AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
description LONGTEXT NOT NULL,
PRIMARY KEY (id)
)
ENGINE = INNODB;
CREATE TABLE ticket_status (
id INTEGER NOT NULL AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
description LONGTEXT NOT NULL,
PRIMARY KEY (id)
)
ENGINE = INNODB;
CREATE TABLE ticket (
id INTEGER NOT NULL AUTO_INCREMENT,
created_at DATETIME NOT NULL,
project_id INTEGER NOT NULL,
created_by INTEGER NOT NULL,
submitted_by INTEGER NOT NULL,
assigned_to INTEGER NULL,
category_id INTEGER NOT NULL,
type_id INTEGER NOT NULL,
title VARCHAR(75) NOT NULL,
description LONGTEXT NOT NULL,
contact_type_id TINYINT NOT NULL,
affects_all_clients TINYINT NOT NULL DEFAULT '0',
is_billable TINYINT NOT NULL DEFAULT '1',
esimated_hours DECIMAL(4, 1) NOT NULL DEFAULT '0',
hours_worked DECIMAL (4, 1) NOT NULL DEFAULT '0',
status_id TINYINT NOT NULL,
PRIMARY KEY (id),
CONSTRAINT fk_ticket_project_id
FOREIGN KEY (project_id)
REFERENCES project (id)
ON UPDATE RESTRICT
ON DELETE RESTRICT,
CONSTRAINT fk_ticket_created_by
FOREIGN KEY (created_by)
REFERENCES user (id)
ON UPDATE RESTRICT
ON DELETE RESTRICT,
CONSTRAINT fk_ticket_submitted_by
FOREIGN KEY (submitted_by)
REFERENCES user (id)
ON UPDATE RESTRICT
ON DELETE RESTRICT,
CONSTRAINT fk_ticket_assigned_to
FOREIGN KEY (assigned_to)
REFERENCES user (id)
ON UPDATE RESTRICT
ON DELETE RESTRICT,
CONSTRAINT fk_ticket_category_id
FOREIGN KEY (category_id)
REFERENCES ticket_category (id)
ON UPDATE RESTRICT
ON DELETE RESTRICT,
CONSTRAINT fk_ticket_type_id
FOREIGN KEY (type_id)
REFERENCES ticket_type (id)
ON UPDATE RESTRICT
ON DELETE RESTRICT,
CONSTRAINT fk_ticket_status_id
FOREIGN KEY (status_id)
REFERENCES ticket_status (id)
ON UPDATE RESTRICT
ON DELETE RESTRICT
)
ENGINE = INNODB;
CREATE TABLE ticket_time_entry (
id INTEGER NOT NULL AUTO_INCREMENT,
user_id INTEGER NOT NULL,
ticket_id INTEGER NOT NULL,
started_at DATETIME NOT NULL,
ended_at DATETIME NOT NULL,
PRIMARY KEY (id),
CONSTRAINT fk_ticket_time_entry_user_id
FOREIGN KEY (user_id)
REFERENCES user (id)
ON UPDATE RESTRICT
ON DELETE RESTRICT,
CONSTRAINT fk_ticket_time_entry_ticket_id
FOREIGN KEY (ticket_id)
REFERENCES ticket (id)
ON UPDATE RESTRICT
ON DELETE RESTRICT
)
ENGINE = INNODB;
The ticket table's create statement causes an error 150. I have no clue why. When I remove the ON DELETE RESTRICT statements from the table declaration, it works. Why is that?
