MySQL for foreign key problems

user_19956493 0
Viewed 21

Am having a problem with my MySQL code, I really don't know why

Am getting this error

#1215 - Cannot add foreign key constraint

The problem is at table sales

CREATE TABLE USERS (
   id INT NOT NULL AUTO_INCREMENT,
   idtel  varchar(100),
   username varchar(100),
   password varchar(100),
   email  varchar(100),
   role  varchar(15),
   credit INT(7),
   a_status varchar(100),
   PRIMARY KEY (id),
   registered datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
   updated datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)ENGINE=INNODB;

CREATE TABLE SALES (
   id INT NOT NULL AUTO_INCREMENT,
   username int,
   profit INT(7),
   sales INT(7),
   payments INT(7),
   balance INT(7),
   PRIMARY KEY (id),
   FOREIGN KEY ( username ) REFERENCES USERS ( username )
) ENGINE=INNODB;
mysql
1 Answers

In MySQL FOREIGN KEY Constraints document we can found that

Corresponding columns in the foreign key and the referenced key must have similar data types. The size and sign of fixed precision types such as INTEGER and DECIMAL must be the same. The length of string types need not be the same. For nonbinary (character) string columns, the character set and collation must be the same.

So the reason is that username in your two tables are not the same,I would suggest you change the username type in your second table

CREATE TABLE SALES (
   id INT NOT NULL AUTO_INCREMENT,
   username varchar(100),
   profit INT(7),
   sales INT(7),
   payments INT(7),
   balance INT(7),
   PRIMARY KEY (id),
   FOREIGN KEY ( username ) REFERENCES USERS ( username )
) ENGINE=INNODB;
user_31764190
Related