CREATE TABLE tbl_project
(
id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(128),
description TEXT,
create_time DATETIME,
create_user_id INTEGER,
update_time DATETIME,
update_user_id INTEGER
);CREATE TABLE IF NOT EXISTS 'tbl_issue'
(
'id' INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
'name' varchar(256) NOT NULL,
'description' varchar(2000),
'project_id' INTEGER,
'type_id' INTEGER,
'status_id' INTEGER,
'owner_id' INTEGER,
'requester_id' INTEGER,
'create_time' DATETIME,
'create_user_id' INTEGER,
'update_time' DATETIME,
'update_user_id' INTEGER
) ENGINE = InnoDB
;CREATE TABLE IF NOT EXISTS 'tbl_user'
(
'id' INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
'email' Varchar(256) NOT NULL,
'username' Varchar(256),
'password' Varchar(256),
'last_login_time' Datetime,
'create_time' DATETIME,
'create_user_id' INTEGER,
'update_time' DATETIME,
'update_user_id' INTEGER
) ENGINE = InnoDB
;CREATE TABLE IF NOT EXISTS 'tbl_project_user_assignment'
(
'project_id' Int(11) NOT NULL,
'user_id' Int(11) NOT NULL,
'create_time' DATETIME,
'create_user_id' INTEGER,
'update_time' DATETIME,
'update_user_id' INTEGER,
PRIMARY KEY ('project_id','user_id')
) ENGINE = InnoDB
;ALTER TABLE 'tbl_issue' ADD CONSTRAINT 'FK_issue_project' FOREIGN KEY
('project_id') REFERENCES 'tbl_project' ('id') ON DELETE CASCADE ON
UPDATE RESTRICT;
ALTER TABLE 'tbl_issue' ADD CONSTRAINT 'FK_issue_owner' FOREIGN KEY
('owner_id') REFERENCES 'tbl_user' ('id') ON DELETE CASCADE ON UPDATE
RESTRICT;
ALTER TABLE 'tbl_issue' ADD CONSTRAINT 'FK_issue_requester' FOREIGN
KEY ('requester_id') REFERENCES 'tbl_user' ('id') ON DELETE CASCADE ON
UPDATE RESTRICT;
ALTER TABLE 'tbl_project_user_assignment' ADD CONSTRAINT 'FK_project_
user' FOREIGN KEY ('project_id') REFERENCES 'tbl_project' ('id') ON
DELETE CASCADE ON UPDATE RESTRICT;
ALTER TABLE 'tbl_project_user_assignment' ADD CONSTRAINT 'FK_user_
project' FOREIGN KEY ('user_id') REFERENCES 'tbl_user' ('id') ON
DELETE CASCADE ON UPDATE RESTRICT;我用的mysql, 我先按照上面的语句,创建了几个表格。表格创建成功后,我设置外建的时候出现了error 1005,上网查了一下,下面是网上说的原因,但是我没找到我这里的语句哪错了,希望明白的人指点一下。“原因之一:设置关联的字段类型不匹配,比如bigint 和 int
解决:外键的相关字段修改成同一类型就可以解决这个问题
原因之二:
解决:1. mysql支持外键约束,数据库类型必须是InnoDB
2. 建外键的表的列(字段)要加上index”
(
id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(128),
description TEXT,
create_time DATETIME,
create_user_id INTEGER,
update_time DATETIME,
update_user_id INTEGER
);CREATE TABLE IF NOT EXISTS 'tbl_issue'
(
'id' INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
'name' varchar(256) NOT NULL,
'description' varchar(2000),
'project_id' INTEGER,
'type_id' INTEGER,
'status_id' INTEGER,
'owner_id' INTEGER,
'requester_id' INTEGER,
'create_time' DATETIME,
'create_user_id' INTEGER,
'update_time' DATETIME,
'update_user_id' INTEGER
) ENGINE = InnoDB
;CREATE TABLE IF NOT EXISTS 'tbl_user'
(
'id' INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
'email' Varchar(256) NOT NULL,
'username' Varchar(256),
'password' Varchar(256),
'last_login_time' Datetime,
'create_time' DATETIME,
'create_user_id' INTEGER,
'update_time' DATETIME,
'update_user_id' INTEGER
) ENGINE = InnoDB
;CREATE TABLE IF NOT EXISTS 'tbl_project_user_assignment'
(
'project_id' Int(11) NOT NULL,
'user_id' Int(11) NOT NULL,
'create_time' DATETIME,
'create_user_id' INTEGER,
'update_time' DATETIME,
'update_user_id' INTEGER,
PRIMARY KEY ('project_id','user_id')
) ENGINE = InnoDB
;ALTER TABLE 'tbl_issue' ADD CONSTRAINT 'FK_issue_project' FOREIGN KEY
('project_id') REFERENCES 'tbl_project' ('id') ON DELETE CASCADE ON
UPDATE RESTRICT;
ALTER TABLE 'tbl_issue' ADD CONSTRAINT 'FK_issue_owner' FOREIGN KEY
('owner_id') REFERENCES 'tbl_user' ('id') ON DELETE CASCADE ON UPDATE
RESTRICT;
ALTER TABLE 'tbl_issue' ADD CONSTRAINT 'FK_issue_requester' FOREIGN
KEY ('requester_id') REFERENCES 'tbl_user' ('id') ON DELETE CASCADE ON
UPDATE RESTRICT;
ALTER TABLE 'tbl_project_user_assignment' ADD CONSTRAINT 'FK_project_
user' FOREIGN KEY ('project_id') REFERENCES 'tbl_project' ('id') ON
DELETE CASCADE ON UPDATE RESTRICT;
ALTER TABLE 'tbl_project_user_assignment' ADD CONSTRAINT 'FK_user_
project' FOREIGN KEY ('user_id') REFERENCES 'tbl_user' ('id') ON
DELETE CASCADE ON UPDATE RESTRICT;我用的mysql, 我先按照上面的语句,创建了几个表格。表格创建成功后,我设置外建的时候出现了error 1005,上网查了一下,下面是网上说的原因,但是我没找到我这里的语句哪错了,希望明白的人指点一下。“原因之一:设置关联的字段类型不匹配,比如bigint 和 int
解决:外键的相关字段修改成同一类型就可以解决这个问题
原因之二:
解决:1. mysql支持外键约束,数据库类型必须是InnoDB
2. 建外键的表的列(字段)要加上index”
-> (id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAR(128),
-> description TEXT,
-> create_time DATETIME,
-> create_user_id INTEGER,
-> update_time DATETIME,
-> update_user_id INTEGER
-> );
Query OK, 0 rows affected (0.13 sec)mysql>
mysql> CREATE TABLE IF NOT EXISTS tbl_issue
-> (id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
-> name varchar(256) NOT NULL,
-> description varchar(2000),
-> project_id INTEGER,
-> type_id INTEGER,
-> status_id INTEGER,
-> owner_id INTEGER,
-> requester_id INTEGER,
-> create_time DATETIME,
-> create_user_id INTEGER,
-> update_time DATETIME,
-> update_user_id INTEGER
-> ) ENGINE = InnoDB
-> ;
Query OK, 0 rows affected (0.09 sec)mysql>
mysql> CREATE TABLE IF NOT EXISTS tbl_user
-> (id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
-> email Varchar(256) NOT NULL,
-> username Varchar(256),
-> password Varchar(256),
-> last_login_time Datetime,
-> create_time DATETIME,
-> create_user_id INTEGER,
-> update_time DATETIME,
-> update_user_id INTEGER
-> ) ENGINE = InnoDB
-> ;
Query OK, 0 rows affected (0.11 sec)mysql>
mysql> CREATE TABLE IF NOT EXISTS tbl_project_user_assignment
-> (project_id Int(11) NOT NULL,
-> user_id Int(11) NOT NULL,
-> create_time DATETIME,
-> create_user_id INTEGER,
-> update_time DATETIME,
-> update_user_id INTEGER,
-> PRIMARY KEY (project_id,user_id)
-> ) ENGINE = InnoDB
-> ;
Query OK, 0 rows affected (0.13 sec)mysql>
mysql> ALTER TABLE tbl_issue ADD CONSTRAINT FK_issue_project FOREIGN KEY
-> (project_id) REFERENCES tbl_project (id) ON DELETE CASCADE ON
-> UPDATE RESTRICT;
Query OK, 0 rows affected (0.20 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> ALTER TABLE tbl_issue ADD CONSTRAINT FK_issue_owner FOREIGN KEY
-> (owner_id) REFERENCES tbl_user (id) ON DELETE CASCADE ON UPDATE
-> RESTRICT;
Query OK, 0 rows affected (0.25 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> ALTER TABLE tbl_issue ADD CONSTRAINT FK_issue_requester FOREIGN
-> KEY (requester_id) REFERENCES tbl_user (id) ON DELETE CASCADE ON
-> UPDATE RESTRICT;
Query OK, 0 rows affected (0.20 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> ALTER TABLE tbl_project_user_assignment ADD CONSTRAINT FK_project_user FO
REIGN KEY (project_id) REFERENCES tbl_project (id) ON
-> DELETE CASCADE ON UPDATE RESTRICT;
Query OK, 0 rows affected (0.20 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> ALTER TABLE tbl_project_user_assignment ADD CONSTRAINT FK_user_project FO
REIGN KEY (user_id) REFERENCES tbl_user (id) ON
-> DELETE CASCADE ON UPDATE RESTRICT;
Query OK, 0 rows affected (0.19 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql>