CREATE TABLE valid (
id SMALLINT NOT NULL AUTO_INCREMENT,
name VARCHAR (200) NOT NULL,
create_time DATETIME NOT NULL,
create_by INTEGER NOT NULL,
change_time DATETIME NOT NULL,
change_by INTEGER NOT NULL,
PRIMARY KEY(id),
UNIQUE INDEX valid_name (name)
);
CREATE TABLE users (
id INTEGER NOT NULL AUTO_INCREMENT,
login VARCHAR (200) NOT NULL,
pw VARCHAR (64) NOT NULL,
title VARCHAR (50) NULL,
first_name VARCHAR (100) NOT NULL,
last_name VARCHAR (100) NOT NULL,
valid_id SMALLINT NOT NULL,
create_time DATETIME NOT NULL,
create_by INTEGER NOT NULL,
change_time DATETIME NOT NULL,
change_by INTEGER NOT NULL,
PRIMARY KEY(id),
UNIQUE INDEX users_login (login)
);
INSERT INTO valid (id, name, create_by, create_time, change_by, change_time)
VALUES
(1, 'valid', 1, current_timestamp, 1, current_timestamp);
INSERT INTO users (id, first_name, last_name, login, pw, valid_id, create_by, create_time, change_by, change_time)
VALUES
(1, 'Admin', 'OTRS', 'root@localhost', 'roK20XGbWEsSM', 1, 1, current_timestamp, 1, current_timestamp);
ALTER TABLE valid ADD CONSTRAINT FK_valid_create_by_id FOREIGN KEY (create_by) REFERENCES users (id);
ALTER TABLE valid ADD CONSTRAINT FK_valid_change_by_id FOREIGN KEY (change_by) REFERENCES users (id);
ALTER TABLE users ADD CONSTRAINT FK_users_create_by_id FOREIGN KEY (create_by) REFERENCES users (id);
ALTER TABLE users ADD CONSTRAINT FK_users_change_by_id FOREIGN KEY (change_by) REFERENCES users (id);
ALTER TABLE users ADD CONSTRAINT FK_users_valid_id_id FOREIGN KEY (valid_id) REFERENCES valid (id);
update users set pw = 'foo' where id = 1;