I have a table with a foreign key. When I try to run a manual query with SQLite inserting 0 into the child key column it fails. However, if i attempt this within the Yii framework (ie setting the child key column to 0 and saving the record) it passes.
Any ideas why? Also, is it recommended that a foreign key column be ‘null’ or 0 if it does not reference a parent row? I am trying to remove the chance that a record with null in this column NOT get recognized if a a lookup is performed with 0 in the column condition.
Your FK column should only accept values that would match up to its related table, or NULL (if the column accepts NULL values). 0 would only be accepted if the related table has a 0 as well, which according to you isn’t the the case at the moment (manual insertion fails).
What do you get when doing it through Yii? Does it succeed but insert a NULL value?
Also, there shouldn’t be a problem with doing a lookup for 0 or NULL since the 2 values are different. Searching for 0 should not give you NULL
The save() is succeeding when either a NULL or 0 value is passed. Since it is working, and the code/DB is built around considering all child rows which do not have a parent as having 0 in that field, I will most likely leave it.
I was using SQLite Data Expert Personal Edition to conduct the manual test. Out of curiosity, I conducted the test via command line on the server, and it successfully accepted 0, even though there was not a record corresponding to the FK.
Not knowing a great deal about constraints when I implemented, I opted for 0 as opposed to NULL due to the fact that much of the data is passed between PHP and Javascript, and once the column contains zero, it will contain zero indefinitely, and I will not have to convert back and forth between an empty string and NULL when persisitng. IE, echoing NULL from php into a function’s parameters would yield an undefined parameter, which JS did not like. Something like myFunction(‘blah’,‘blah’) would throw errors.
If anyone has any feelings on this, I’d love to hear them, if only for growth as a programmer.
