So I don't understand why I cannot insert datas 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 datas 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)
)
Any idea what I am doing wrong??
Thanks i advance!!!
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.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments