0

I have run into the error while constructing multiple database table structure. I have 'user' table, and it's parent table of other tables.

- USER -
email VARCHAR(50) DEFAULT '' NOT NULL;
login_flag VARCHAR(45) DEFAULT '' NOT NULL;
session_id VARCHAR(40) DEFAULT '0' NOT NULL;
name VARCHAR(20) DEFAULT '' NOT NULL;
...
PRIMARY KEY (email, login_flag);

When I execute queries below, they are executed well.

CREATE TABLE ARTICLE
(
id INT(13) PRIMARY KEY NOT NULL AUTO_INCREMENT,
email VARCHAR(50) DEFAULT '0' NOT NULL,
login_flag VARCHAR(45) DEFAULT '' NOT NULL,
content TEXT NOT NULL,
...
write_date DATETIME NOT NULL,
CONSTRAINT community_article_user_email_login_flag_fk FOREIGN KEY
(email, login_flag) REFERENCES user (email, login_flag)
);
CREATE INDEX community_article_user_email_login_flag_fk ON community_article (email, login_flag);


CREATE TABLE ARTICLE_LIKE
(
article_id INT(13) DEFAULT '0' NOT NULL COMMENT 'content id(target id)',
email VARCHAR(50) DEFAULT '0' NOT NULL,
login_flag VARCHAR(45) DEFAULT '' NOT NULL,
make_date DATETIME DEFAULT 'CURRENT_TIMESTAMP',
CONSTRAINT community_article_like_community_article_id_fk FOREIGN KEY (article_id) REFERENCES community_article (id),
CONSTRAINT community_article_like_user_email_login_flag_fk FOREIGN KEY (email, login_flag) REFERENCES user (email, login_flag)
);
CREATE INDEX community_article_like_community_article_id_fk ON community_article_like (article_id);
CREATE INDEX community_article_like_user_email_login_flag_fk ON community_article_like (email, login_flag);

But, I execute the query below to build table, It's failed with error messages [[HY000][1215] Cannot add foreign key constraint].

CREATE TABLE BOOKMARK
(
item_id INT(13) NOT NULL,
email VARCHAR(50) DEFAULT '' NOT NULL,
login_flag VARCHAR(45) DEFAULT '' NOT NULL,
make_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
CONSTRAINT FK FOREIGN KEY (email, login_flag) REFERENCES user (email, login_flag)
)

I totally don't know why this is failed. I think there is no difference between the latter and the former.

Knunu
  • 315
  • 1
  • 3
  • 6
  • What about character sets in the 2 tables? Moerover, I would introduce a generated, numeric userid field in the users table as pk. It would save you a lot of trouble and overhead while creating foreign keys. – Shadow May 03 '16 at 08:54
  • Possible duplicate of [Cannot add foreign key constraint - MySQL ERROR 1215 (HY000)](http://stackoverflow.com/questions/29248057/cannot-add-foreign-key-constraint-mysql-error-1215-hy000) – krokodilko May 03 '16 at 09:03
  • @knunu , You can make a composite/multiple foreign keys but Check whether you have indexed the column . – Rajesh May 03 '16 at 10:02
  • @Shadow they have same character set 'UTF-8'. anyway, you recommend to use user-id as pk in user table, right? – Knunu May 03 '16 at 10:05
  • @Rajesh you mean, It should be first to set a index to the column in foriegn key set? – Knunu May 03 '16 at 10:07
  • @kordirko thx, I will refet that. – Knunu May 03 '16 at 10:07
  • @Knunu , The referenced parent field must have an index defined on it. – Rajesh May 03 '16 at 10:08
  • @Rajesh thank you for reply. but If index could be problem, other query can't be executed well. – Knunu May 03 '16 at 10:43
  • @Rajesh the 2 fields in the user table are defined as PK, which creates an index on both fields. – Shadow May 03 '16 at 10:49
  • @Knunu that's right, create a userid field in the users table. You can have a separate unique index on the email field. – Shadow May 03 '16 at 10:49

0 Answers0