Oh my... FK question..

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
waskelton4
Forum Contributor
Posts: 132
Joined: Mon Sep 09, 2002 6:42 pm

Oh my... FK question..

Post by waskelton4 »

Ok..

so I have this application that has been running at work and collecting data from users for 3 years now. It is my first large php/mysql project but it started out fairly small.

It was initially just a simple list but now has evolved into what could potentially become a mission critical application.

Since the data in it stands to become more official in regards to my organization I've converted all of my tables to innoDB and I'm in the process of implementing Foreign Key constraints to many many of the tables in the database to get rid of as much bad data as I can.

this is my problem.. (in a nutshell)

Most all of my parent tables have ID rows that are auto incrementing. most all of my child tables that link to these parent tables have values of 0 in the records where a parent row isn't associated with that child row. These 0 values break the foreign key constraint and aren't allowed.

I could go through the entire database and update everything that has a value of 0 to be NULLl so as to not break the constraint but that means that i'll also have to go and update ALL of my sql code in the application to insert and update values of NULL instead of '0'. This wouldn't be a big deal but because my sql code currently looks like this..

Code: Select all

$sql = "UPDATE tblTable SET field1 = '$_POST[selType]', feild2 = '$_POST[txtName]' WHERE field_id = $_SESSION[id]";
which would create the sql

Code: Select all

UPDATE tblTable SET field1 = 'NULL', feild2 = 'Users Name' WHERE field_id = 565;
and the 'NULL' would be interpreted as character values and therefore would also break the foreign key constraints.

I can see a number of ways this could have been avoided but thats all in the past now and I'm really only concerned about how to fix this problem.

I guess one of my options is to go and in every parent table create a row with key values of "None" and make that row's ID the default and use that ID where there are currently values of 0 or NULL in the table. I'm not really wild about this solution because it skews the data and gives a value to fields that should actually have no data in them.

What is the best way to handle this? What is the best practice regarting this sort of situation?

All help and replies are greatly appreciated!

Thanks
Will
waskelton4
Forum Contributor
Posts: 132
Joined: Mon Sep 09, 2002 6:42 pm

Post by waskelton4 »

ok.. so i've looked around a little more..


and it appears that my solution (as painful as it seems) is gonna be to go through and remove all of the single quotes for all of my sql statements where numbers are being inserted or updated. This should fix the problem since (i think) all of my PK-FK relationships are with integers.


If anyone has any comments or advice I'd still love to hear it.

Thanks
Will
dreamline
Forum Contributor
Posts: 158
Joined: Fri May 28, 2004 2:37 am

Post by dreamline »

Ehm, my experience with php/mysql is that you can insert/update integers with or without quotes into the database....But to be sure i checked it against my version of mysql and i updated some bigint values with and without quotes and they got nicely updated....
Last edited by dreamline on Mon Oct 31, 2005 10:06 pm, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

MySQL implicitly converts strings to numerics when and where possible, although I don't know whether that extends into foreign keys..
waskelton4
Forum Contributor
Posts: 132
Joined: Mon Sep 09, 2002 6:42 pm

Post by waskelton4 »

right..

and thats not a big deal..

the problem is that neither mysql (as far as i know or can tell) converts 'NULL' from a string to an actual NULL value.

ws
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

corrent, it does not. You should emit NULL without quotes when possible..
Post Reply