CODEWITHSUNDEEP
mysqlsqldatabasedatabase-design
Vektor
Vektor

Reputation: 580

MySQL database with two foreign keys in one table

I'm fairly new to database designs, but I read you could have two foreign keys in one table. For some reason I'm getting an error while trying to run the following SQL in phpMyAdmin.

CREATE TABLE tbl_user
(
  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(45) NOT NULL,
  password VARCHAR(45) NOT NULL,
  email VARCHAR(72) NOT NULL,
  first_name VARCHAR(45),
  last_name VARCHAR(45),
  role VARCHAR(20),
  UNIQUE(username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE tbl_profile
(
  id INTEGER NOT NULL PRIMARY KEY,
  attribute VARCHAR(128) NOT NULL,
  value VARCHAR(128) NOT NULL,
  CONSTRAINT FK_userid FOREIGN KEY (id)
    REFERENCES tbl_user (id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT FK_attribute FOREIGN KEY (attribute)
    REFERENCES tbl_attribute (id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE tbl_attribute
(
  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(45) NOT NULL,
  description TEXT,
  data_type VARCHAR(45)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Any help is greatly appreciated!

Upvotes: 2

Views: 4645

Answers (2)

spencer7593
spencer7593

Reputation: 108530

There's a mismatch in the datatypes of the foreign key column and the primary key column; these have to match.

tbl_profile(attribute)  is VARCHAR
tbl_attribute(id)       is INT

Also, the referenced table has to be created before you create the foreign key constraint. (We often see the foreign key constraints defined separately, in ALTER TABLE statements, after the tables are created.)

Upvotes: 3

Dennis Leon
Dennis Leon

Reputation: 163

Try creating tbl_attribute before you create tbl_profile.

Upvotes: 3

Related Questions