Cannot INSERT because of Foreign key constraints - mysql

Question

So I don't understand why I cannot insert data in my table that have foreign constraint keys or even modify anything in it.

Here is an example of the tables that are created. I am trying to insert data in the addresses table:

///////////////////////ADDRESSES TABLE ////////////////////////
CREATE TABLE IF NOT EXISTS addresses (
                id INT NOT NULL AUTO_INCREMENT,
                addressline1 VARCHAR(255) NOT NULL,
                addressline2 VARCHAR(255) NOT NULL,
                postcode VARCHAR(255) NOT NULL,
                phonenumber INT(13) NOT NULL,
                
                country_id INT NOT NULL,
                
                PRIMARY KEY (id),
                
                FOREIGN KEY (country_id) REFERENCES countries(id)
                
                  ON UPDATE CASCADE
                  ON DELETE RESTRICT
                
                ) ENGINE=InnoDB ";

///////////////////////COUNTRIES TABLE ////////////////////////
CREATE TABLE IF NOT EXISTS countries (
                id INT NOT NULL AUTO_INCREMENT,
                countryname VARCHAR(255) NOT NULL,

                PRIMARY KEY (id)
                
                )

Just a guess... are you trying to insert into addresses before you insert into countries? — The Velcromancer
– The Velcromancer, Commented Jun 22, 2014 at 23:36
Yes I do. So I can't insert in the child table before the parent one? What is the correct way to do it then? Because the addresses table is also a parent table for my Users table... Should I put the foreign keys of my table as NULL in order to fill datas without having issues? Also I cannot even set the country_id as NULL after tables have been created...? — commandantp
– commandantp, Commented Jun 23, 2014 at 11:23
2. Also was wondering; does it make more sense to have the foreign key addresse_id in the users table or have a foreign key user_id in the addresses table...? — commandantp
– commandantp, Commented Jun 23, 2014 at 12:42

The Velcromancer · Accepted Answer · 2014-06-23 12:57:22Z

1

The issue here is that you are trying to insert into a referencing table (addresses) when the referenced entry (the country you reference) does not exist. That's what's triggering the FOREIGN KEY CONSTRAINT exception.

Try first inserting some countries into the countries table, then inserting some addresses where you reference those countries you entered in the first step.

As for your second question, that's a choice for you to make. I would probably choose to have the User have an Address (address field in the User table), but some of that depends on how the data is being used/updated.

Have a quick look through this resource if you're new to relational database design. It covers (in brief) topics like relationship types, key constraints, and normal forms.

answered Jun 23, 2014 at 12:57

The Velcromancer

4473 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

commandantp Over a year ago

Ok will do that in the right order then. I am new to working with the DB but not to the concept/relationships thus the errors... What if my user has no address when being created ? Is it good practice to set the foreign key as NULL when creating tables to avoid errors if no address is yet set up? Thanks a lot,

The Velcromancer Over a year ago

Try taking a look at this stack overflow post to help with that question :)

commandantp Over a year ago

Ok very helpful your link too for the examples on a complete database see here. I have been looking for an example like that for a while now..!

The Velcromancer Over a year ago

I'm glad it's been helpful for you! Good luck on your project :)

Collectives™ on Stack Overflow

Cannot INSERT because of Foreign key constraints - mysql

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related