Need trick to avoid foreign key constraint.

[ansuz]

I have foreign key constraints in the database I'm working on. For example 'type' is a foreign key that may be null.

type CHAR (10) default NULL

The 'type' value is read from an html list as a string. My query works nicely when the submitted value isn't null.

INSERT INTO table (type) VALUES ('$type')

However I have problem when the selected value is null. In that case my query is expanded to something like this:

INSERT INTO table (type) VALUES ('')

Well too bad for me. '' is not null at all. That's an empty string. My query fails because it doesn't satisfy my foreign key constraint.

I see 3 possible solutions but none of them is magic enough for me. Do you have any other idea?

1. Use a numeric foreign key, etc. I don't want to touch my db design, so that's not an option I like.

2. Add an empty string record in my type table.

3. Test the value obtained from the html form. If it's not null, add quotes around it. Submit without quotes, like:

INSERT INTO table (type) VALUES ($type)

Not too bad, but since I quote every value in submit in my queries, I'd like to avoid tricks like that. Maybe that's a bad habit I have?

Thanks for your feedback!

[ansuz]

Another possibility would be to submit quotes with my values:

<option value="">
<option value="'val1'">option 1
<option value="'val2'">option 2

And then I could just

INSERT INTO table (type) VALUES ($type)

I would have to rewrite all my forms to use this standard. Not very fun, and not very good standard to apply I think.

[diego25]

I'd go with option 3. Something like this:

<?
function formatStr($str) {
  if ($str == '') return 'NULL';
  else return "'$str'";
}

$query = 'INSERT INTO table (type) VALUES (' . formatStr($type) . ')';
?>

This way, if you later change your db design or something, you just need to change the formatStr() function.

Diego

[ansuz]

What I did in the end is to replace empty quotes for NULL in my query string with strreplace.