1

I have created a simple users table which is having userid and username(unique).

Created another table units, this table contains cratedby which i want to record username in users table. I created a foreign key field. createdby with username in users. Its done without any issue.

Now I created another table customers1 which is also having a field cratedby(one of the username). When I am establishing foreign key relation its throwing an error - foreign key constaint is incorrectly formed.

There is no data in any of the tables. Why its working incase of units table not in customers1 table. I am uploading 3 images.

Please help... I read similar questions tried several options, but did not work. 3 Tables, HeidiSQL

kpkdhar22
  • 11
  • 2
  • This is a faq. Please tag your SQL. Read its official documentation & google many different phrasings of your error/question/problem/desiderata, not just 'several'. Can username be null in the users tables? Please read & act on [mcve]. Please [use text for text & tables, not images & links].(https://meta.stackoverflow.com/a/285557/3404097). – philipxy Sep 07 '17 at 18:50

1 Answers1

0

First thing first, don't use Username as a primary key instead use userid, as usernames could be similar.

Second A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table. And in your table userid is the Primary key (with yellow key icon on it), not the username.

If you could use a console do use that instead. And do the following

-> CREATE TABLE USERS(USERID INT PRIMARY KEY AUTO_INCREMENT, USERNAME VARCHAR(30));
-> CREATE TABLE UNIT (UNITID INT PRIMARY KEY, USERID INT,FOREIGN KEY (USERID) REFERENCES USERS (USERID));
-> CREATE TABLE CUSTOMER (CUSTOMERID INT PRIMARY KEY, USERID INT, FOREIGN KEY (USERID) REFERENCES USERS (USERID));
Nikhil Wagh
  • 1,376
  • 1
  • 24
  • 44