Trouble with using foreign keys in MYSQL database

I am having trouble using foreign keys in mySQL. I can't see anything wrong with code that I have made but when I insert data into the table with the foreign key(company table(userid) it doesn't work. Sorry for being vague..first time asking here.

CREATE TABLE users 
(
userid INT(4) NOT NULL auto_increment,
username VARCHAR(30) NOT NULL,
email VARCHAR(32) NOT NULL,
password VARCHAR(32) NOT NULL,
companyName VARCHAR(32) NOT NULL,
address VARCHAR(30) NOT NULL,
PRIMARY KEY (userid)
) ENGINE=INNODB;





DROP TABLE IF EXISTS company;

CREATE TABLE company 
(
userid int(4) NOT NULL,
companyid INT(4) NOT NULL auto_increment,
companyName VARCHAR(32) NOT NULL,
address VARCHAR(30) NOT NULL,
telephone INT(20) NOT NULL,
email VARCHAR(30) NOT NULL,
textbody VARCHAR(1800) NOT NULL,
textbody2 VARCHAR(1500) NOT NULL,
PRIMARY KEY (companyid),
FOREIGN KEY (userid) REFERENCES users(userid)
) ENGINE=INNODB;

Here is the INSERT STATEMENT

INSERT INTO company (companyName, address, telephone, email, textbody, textbody2)VALUES 
('blaaah', 'Ireland', '12345', 'emailAddress', 'randomtext', 'morerandomtext');

Here is the error

#1452 - Cannot add or update a child row: a foreign key constraint     fails(`users`.`company`, CONSTRAINT `company_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `    users` (`userid`))

Answers


You can't insert a row with a particular userid value into your company table until you've inserted a row with that same value into your users table. That's what the FK constraint enforces.

The INSERT INTO company statement you show in your question does not specify the value of userid. If you're using foreign key constraints, you must specify that value, and it must be one of the values that's present in your users table.

How do you come up with a valid userid value? That's hard to answer precisely without understanding your business logic: why must each company row necessarily refer to exactly one users row?

One way to solve the problem is to INSERT the company row immediately (immediately! with no intervening INSERT statements!) after you INSERT the users row. In this case the MySQL function LAST_INSERT_ID() will contain the appropriate autoincremented value.

INSERT INTO users
       (username, email, password, company, address)
VALUES ('mickey', 'mickey@disney.com', 'ILuvMinnie', 'Walt Disney', 'Burbank');

INSERT INTO company 
       (userid, 
        companyName, address, telephone, 
        email, textbody, textbody2)
VALUES (LAST_INSERT_ID(),   /* from the previous insert */
       'Walt Disney', 'Burbank, CA', '203 555 1212',
       'info@disney.com', 'The Happiest Place on Earth.', 'morerandomtext');

Please try this code: SQLFiddle

I use number format for telephone and last_insert_id() in my insert statement.

insert into users (username, email, password, companyName, address) 
values ('test', 'test@test.com', 'ghghb', 'test corp', 'test');

insert into company (userid, companyName, address, telephone, email, textbody, textbody2) 
values (last_insert_id(), 'test','test', 123456, 'test@test.com', 'test', 'test');

Please check your constrait statement for company table and check this link.

I think you don't add userId to INSERT statement when insert new row to company. But you should add one existed userId.

Based on comments:

If you want to insert new company rows without userId, please define table structrure like as:

CREATE TABLE company 
(
userid int(4),
companyid INT(4) NOT NULL auto_increment,
companyName VARCHAR(32) NOT NULL,
address VARCHAR(30) NOT NULL,
telephone INT(20) NOT NULL,
email VARCHAR(30) NOT NULL,
textbody VARCHAR(1800) NOT NULL,
textbody2 VARCHAR(1500) NOT NULL,
PRIMARY KEY (companyid),

) ENGINE=INNODB; 

In this case I don't define foreign key. You can insert with or without userId new company rows.


Need Your Help

Can't use Amazon S3 API over SSL?

java amazon-web-services ssl amazon-s3

I'm trying to use Amazon S3 API to encrypt and upload a file.

Find result in between date range in mysql not working properly

php mysql

I've made a query below but it fetch only one row while there are two rows between this date range in database: