Inserting data into a table with a Foreign key

Question

0.00/5 (No votes)

See more:

Hi, I've created all my tables for a database however I am manually inserting 20 random records,
I am trying to insert the data into my table which is:

create table ACCOUNT (acc_ID int NOT NULL, acc_name varchar(20) NOT NULL, acc_manager varchar(20) NOT NULL, acc_balance DECIMAL(6) NOT NULL, acc_transationLimit int NOT NULL, acc_creditlimit int NOT NULL, out_ID int NOT NULL, PRIMARY KEY (acc_ID), FOREIGN KEY (out_ID) REFERENCES OUTLET (out_ID));

im using secure shell to implement the sql and i am inputting:

(' 000 ', 'account1', ' parker ', ' 000.000', ' 99999 ', ' 50000 '),
(' 001 ', ' account2 ', ' kate ', ' 200.000 ', ' 99999 ', ' 40000 '),
(' 002 ', ' account3 ', ' fred ', ' 240.000 ', ' 99999 ', ' 20000 '),
(' 003 ', ' account4 ', ' cat ', ' 500.000 ', ' 99999 ', ' 60000 '),
(' 004 ', ' account5 ', ' parker ', ' 350.000 ', ' 99999 ', ' 50000 '),
(' 005 ', ' account6 ', ' kate ', ' 120.000 ', ' 99999 ', ' 40000 '),
(' 006 ', ' account7 ', ' fred ', ' 100.000 ', ' 99999 ', ' 20000 '),
(' 007 ', ' account8 ', ' cat ', ' 090.000 ', ' 99999 ', ' 60000 '),
(' 008 ', ' account9 ', ' parker ', ' 400.000 ', ' 99999 ', ' 50000 '),
(' 009 ', ' account10 ', ' kate ', ' 900.000 ', ' 99999 ', ' 40000 '),
(' 010 ', ' account11 ', ' fred ', ' 340.000 ', ' 99999 ', ' 20000 '),
(' 011 ', ' account12 ', ' cat ', ' 850.000 ', ' 99999 ', ' 60000 '),
(' 012 ', ' account13 ', ' parker ', ' 400.000 ', ' 99999 ', ' 50000 '),
(' 013 ', ' account14 ', ' kate ', ' 250.000 ', '99999 ', ' 40000 '),
(' 014 ', ' account15 ', ' fred ', ' 460.000 ', ' 99999 ', ' 20000 '),
(' 015 ', ' account16 ', ' cat ', ' 210.000 ', ' 99999 ', ' 60000 '),
(' 016 ', ' account17 ', ' parker ', ' 660.000 ', ' 99999 ', ' 50000 '),
(' 017 ', ' account18 ', ' kate ', ' 410.000 ', ' 99999 ', ' 40000 '),
(' 018 ', ' account19 ', ' fred ', ' 000.000 ', ' 99999 ', ' 20000 '),
(' 019 ', ' account20 ', ' parker ', ' 100.000 ', ' 99999 ', ' 50000 ');

However I've been given this error:

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`JM`.`ACCOUNT`, CONSTRAINT `ACCOUNT_ibfk_1` FOREIGN KEY (`out_ID`) REFERENCES `OUTLET` (`out_ID`))

Posted 25-Jan-16 9:44am

Member 12286728

Add a Solution

Comments

PIEBALDconsult 25-Jan-16 15:50pm

Have you populated OUTLET?

ZurdoDev 25-Jan-16 15:54pm

1. You have this tagged as both SQL and MySql. Which one is it?
2. This means you are trying to put a value into out_ID that does not exist in the lookup table.

PIEBALDconsult 25-Jan-16 16:30pm

MySQL is one of many implementations of the SQL standard. :D

ZurdoDev 25-Jan-16 16:31pm

And SQL is most often used to refer to Microsoft SQL. ;)

I should have been more clear.

PIEBALDconsult 25-Jan-16 16:35pm

Everyone should speak more precisely. "SQL" does not mean any individual product. When I learned SQL, we had only Oracle and DBase available.

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Purna Chandra Sasmal · Answer 1 · 2016-01-25T10:47:00

Solution 1

First populate the table OUTLET and make sure all outlet info you are inserting in parent table must be already available in outlet table. Only then we can insert records into your primary table.

Otherwise, we can remove the outlet value with null in your query which create the issue .

Posted 25-Jan-16 10:47am

Purna Chandra Sasmal

Sahan2u · Answer 2 · 2016-01-26T16:45:00

As mention in the Solution 1 you may going to insert a 'out_ID' which is not in the parent table (OUTLET table).

To check this you can temporarily disable foreign key constraint in sql server and try to insert data.

SQL

-- Disable single constraint

ALTER TABLE Table NOCHECK CONSTRAINT ConstraintName

-- Enable single constraint

ALTER TABLE Table CHECK CONSTRAINT ConstraintName

-- Disable all table constraints

ALTER TABLE Table NOCHECK CONSTRAINT ALL

-- Enable all table constraints

ALTER TABLE Table CHECK CONSTRAINT ALL

If it is worked then definitely the issue is you are going insert out_id which is not in the OUTLET table.

Inserting data into a table with a Foreign key

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Inserting data into a table with a Foreign key

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us