CODEWITHSUNDEEP
javamysqljdbc
Mike
Mike

Reputation: 1610

JDBC connecting to MySQL keeps telling me I am breaking foreign key constraint? Wha?

Okay, so I keep getting this error:
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails

And it's really frustrating and I don't know why? All I did was assign a foreign key to a primary key in another table (the books table to be precise). Any pointers would be greatly appreciated.

        //create patron table
        s.executeUpdate ("DROP TABLE IF EXISTS patron");
        s.executeUpdate (
            "CREATE TABLE patron ("
            + "patron_ID CHAR(10),"
            + "PRIMARY KEY (patron_ID),"
            + "fName CHAR(50)NOT NULL, MI CHAR(2), lName CHAR(75)NOT NULL,"
            + "street_Name CHAR(100)NOT NULL, city CHAR(50) NOT NULL, state CHAR(50)NOT NULL,"
            + "zip_Code CHAR(10) NOT NULL, home_Phone CHAR(12)NOT NULL, call_Number CHAR(10)NOT NULL,"
            //+ "FOREIGN KEY (call_Number) references corejava.book(call_Number))");
            + "FOREIGN KEY (call_Number) references " + dbName + ".book (call_Number))");

        count2 = s.executeUpdate (
            "INSERT INTO patron"
            + " VALUES"
            + "('P222200000', 'Harry', 'P','Bradford','1234 Street Place','Silver Spring','Maryland','20906','301-555-9999','MY.111.0001'),"
            + "('P222200001', 'Sally','','Titus','2365 Huckleberry Lane','Silver Spring','Maryland','20906','301-554-9896','SF.111.002'),"
            + "('P222200002', 'Mark', 'C','Bradley','654 Finance Lane','Silver Spring','Maryland','20906','301-665-4978','AV.111.004'),"
            + "('P222200003', 'Carlos','','Iglesias','987 Potter Place','Silver Spring','Maryland','20906','240-702-5648','CO.111.006'),"
            + "('P222200004', 'Chris','','Craig','951 Einstein Way','Silver Spring','Maryland','20906','301-569-4415','IN.111.008'),"
            + "('P222200005', 'Samantha','A','Brikmon',' 8380 Colesville Road','Silver Spring','20906','301-659-5569','CO.111.007'),"
            + "('P222200006', 'Lindsey', '','Saucer','12507 Winexburg Manor Drive','Silver Spring','Maryland','20906','301-632-6635','SF.111.003'),"
            + "('P222200007', 'Judy', 'A','Freud','2518 Woodedge Road','Glenmont','Maryland','20905','240-985-9632','AV.111.005'),"
            + "('P222200008', 'Elizabeth','M','Longhorn','552 Lockwood Drive','Wheaton','Maryland','20904','202-555-6639','IN.111.009'),"
            + "('P222200009', 'Gabriella', 'S','Young','654 Glenallen Road','Silver Spring','Maryland','20906','301-555-9898','MY.111.000')");

        System.out.println (count2 + " rows were inserted");
        s.close();

Upvotes: 0

Views: 472

Answers (4)

paxdiablo
paxdiablo

Reputation: 882426

Execute:

select * from book where call_number = 'MY.111.0001';

for each of the call numbers (MY.111.0001 above but substitute in each in turn) you're trying to insert into patron to ensure the row exists.

That should show you which particular record is causing the constraint violation, at which point you either use a different call number or insert a row for that call number into book.

Alternatively, you could execute:

select call_number from book where call_number in (
    'MY.111.0001',
    'SF.111.002',
    'AV.111.004',
    'CO.111.006',
    'IN.111.008',
    'CO.111.007',
    'SF.111.003',
    'AV.111.005',
    'IN.111.009',
    'MY.111.000')

and see which of those don't show up in the output.

Looking at the length of that first one once they're nicely lined up, I suspect it's a problem - perhaps it should be MY.111.001?

Upvotes: 1

RichardTheKiwi
RichardTheKiwi

Reputation: 107806

What it means is that in your insertions to the patron table, in the last column (e.g. first record has 'MY.111.0001'), there are some values that do not exist in book.call_number. That is the purpose of foreign keys, to make sure data is consistently referenced! Allowing the insert would leave some value of patron.call_number to be invalid.

Upvotes: 2

Ted Hopp
Ted Hopp

Reputation: 234857

You're trying to insert a record in patron that has a call_Number that does not exist in any row of table book.

Upvotes: 2

Mike Thomsen
Mike Thomsen

Reputation: 37526

Are the values you are putting into patron.call_number the same as the ones in book.call_number?

Upvotes: 1

Related Questions