Re: Jet SQL: Insert Null FK?
Don't use MAX() ... there's no reason to. Fillings is unique... so you're not going to get more than one... you will get one or nothing...
-tg
Re: Jet SQL: Insert Null FK?
If you don't use MAX() it fails to work as desired. That new Pies row will never get inserted into the table because the SELECT returns no rows.
To clarify:
MAX() isn't really MAX() here, it forces the SELECT to be an aggregation query and thus always return a row. Since MAX(NULL) is NULL this works out fine.
It just looks ugly and is likely to confuse someone coming along later. They'd probably chuckle, remove MAX(), and break the entire thing.
Re: Jet SQL: Insert Null FK?
Quote:
Originally Posted by
dilettante
If you don't use MAX() it fails to work as desired. That new Pies row will never get inserted into the table because the SELECT returns no rows.
To clarify:
MAX() isn't really MAX() here, it forces the SELECT to be an aggregation query and thus always return a row. Since MAX(NULL) is NULL this works out fine.
It just looks ugly and is likely to confuse someone coming along later. They'd probably chuckle, remove MAX(), and break the entire thing.
Which is exactly what I did... :D
hmmm.... I see your point...
-tg
Re: Jet SQL: Insert Null FK?
This came up because an old customer had let their own people work on a program I wrote a few years ago for them. It bulk-loads data exported from a legacy system, yes even older than VB6 and Jet, and still in service. ;)
I had warned them at the time about the potential risk of NULL foreign key values, but I didn't have too many alternatives to offer. I suggested adding a "Filling" value like "*none*" or " " but they rejected that idea. They only found this after an audit earlier this year uncovered hundreds of lost records (in the real database) after many years of "Something is wrong here."
So rather than scary code comments, I was wondering whether some alternative SQL might help make the intent more obvious. And like you, the MAX() looks nutty to me as well.
So I'm still trying to find another approach that isn't even more likely to "get fixed" down the road by some programmer.
Re: Jet SQL: Insert Null FK?
I'd look at it from the other side: Do you need a "real" Foreign Key in "Pies"?
As in: To me it looks like a 1:1 or 1:0-Relation between "Pies" and "Fillings" (A "Pie" can have only one or none "Filling").
The general use of Foreign Keys is to preserve integrity in the detail-table when updating or deleting records in the master-table.
I can't imagine you needing this mechanism in this case.
You have three "Pies" with "Cherry"-Filling.
Delete "George Washington" and a update/delete-mechanism would delete the "Filling" Cherry, breaking the other two pies (or complaining to the user:
"Oops, sorry, no! No deleting, since cherry is still in use"). Your constraint says: "ON DELETE SET NULL" which is what you actually want (saw that in a second run through your code)
That said: You could also argue, that "Filling" is the Main-Table, and the "Pies" are the "Detail", since there would actually be a 1:n-relation.
Delete Cherry, and delete all Pies containing cherry (Say: "It's out of season. Not available!").
If you really don't need the "ON UPDATE"-Constraint, have you thought about declaring "FillingID" in "Pies" not as a FK, but a regular field?
That way i don't think you would need this MAX()-Thingy in your query, because you could just set it to NULL from your Frontend-Code.
EDIT: I'm an idiot: Have you thought about a LEFT JOIN?
As in:
1) create a Table "tmpPie" and upload your Pie-File as TextFields
2) In your PROC where you actually assign the ForeignKey (why per Pie?), you can get the Values with
SELECT P.Pie, F.FillingID,NOW()
FROM Pie P
LEFT JOIN Filling F
ON
P.Filling=F.Filling
3) Drop Table "tmpPie"
That should leave you with a NULL for FillingID where there is no match.
Additionally, you would do it in Bulk and not per Pie.
As a second idea:
Remove the ForeignKey from "Pie", but introduce a intermediary table between Pie and Filling (as like you would do for a m:m-relation)
and this table has ForeignKeys to both, Pie and Filling, with the full constraints ON UPDATE/DELETE CASCADE,
so if a Filling gets deleted, you still have your Pie with no dangling ForeignKey pointing to nowhere
Re: Jet SQL: Insert Null FK?
It all depends on what the scenario demands. If you want to be able to delete "George Washington" without deleting "Cherries" (for all rows that reference "Cherries") then a redesign is required.
You can't just remove ON DELETE SET NULL (which implies NO ACTION) because then you can't remove the "George Washington" row at all (error and rollback).
As you suggest, the answer might be to use a JOIN instead or it might be to get rid of the FK entirely and use a plain ID field.
As far as getting rid of MAX() goes... relying on the front-end was not an option in the real database. DBA was firmly against that.
The "real" database is SQL Server however the DBA tries to keep things as generic as possible. Their programmers (who are all.. untrained, i.e. recruited from the ranks of Office VBA plinkers) do their development and testing against small Jet databases. It's a small shop where the "DBA" and his backup are Jacks Of All Trades so to speak, the only two people with a strong development background. They have 5 or 6 part-time "programmers" to handle front-end grunt work, and maybe 10 other employees who can be assigned part time as testers.
Since those two "DBA" guys have almost all of the responsibility they can get pretty cranky about "innovation." To minimize data loss they run backups and data exports twice a day (Noon and end of day). If they had to support 24 by 7 updating I think they'd lose their minds.
Re: Jet SQL: Insert Null FK?
The more I look at it the more likely it seems that a 3rd "link" table may be required to make the "delete GW without deleting Cherries" work.
Re: Jet SQL: Insert Null FK?
Quote:
Originally Posted by
dilettante
The more I look at it the more likely it seems that a 3rd "link" table may be required to make the "delete GW without deleting Cherries" work.
You mean, as in my second idea?
I agree, it would be the "cleanest" way to skin that cat.
you would just have to change/update those two views and the AssingForeignKey-procedure.
And i actually agree with that DBA: I, too, try to get as many things done by the backend contrary to the frontend (Triggers, constraints, SP's etc.)
Re: Jet SQL: Insert Null FK?
Your problem is that your insert depends on the select but there's no real need to do that as you always want exactly one row. Instead, do an insert from Values and have a sub query return the value for the foreign key.
Don't know if this is valid for jet but should work I think:-
Code:
Insert into Pies (Pie, FillingID, `TimeStamp`)
Values (NewPie, (Select FillingID From Fillings Where Filling = PieFilling), Now())
(I typed straight into the forum so watch for syntax errors)
Re: Jet SQL: Insert Null FK?
FD, was thinking about that, too, but it would still be fired for each new "Pie".
And i'm a fan of "bulk"......
Albeit, it would actually be the smallest change to the code.
Re: Jet SQL: Insert Null FK?
You can use the above in a bulk scenario:-
Quote:
Insert into Pies (Pie, FillingID, `TimeStamp`)
Select NewPie, (Select FillingID From Fillings Where Filling = PieFilling), Now()
From MySourceOfBulkData
The point's not really what you're inserting from, the point is that the look up to FillingID needs to be in a subquery so that it can return a null when the filling's not found rather than failing to return a row.
Quote:
I had warned them at the time about the potential risk of NULL foreign key values
There's an argument about whether Nulls should be allowed on databases at all but, if you are allowing nulls, then there's nothing wrong with a null in a foreign key. A foreign key is a value like any other and can be viewed, in OO terms, as a complex type variable rather than a simple type variable. A null simply indicates the lack of a value for that variable, whether it's complex or simple.
I think some confusion comes from the wording in your design. Your fillings table isn't really about fillings, it's about categorisations of pie. "Dil's Famous American Apple Pie" has a foreign key to the Apple row not because it's filling is apple (it's got cinnamon in it too, and maybe some blackberries) but because you've chosen to categorise it as an "Apple Pie".
So you really need to decide whether a pie can have more than one category or no category at all. If your pies can only belong to one category and are allowed to be uncategorised then your design is correct. If Pies have to belong to a category then you need a not null constraint on the foreign key. If they're allowed to belong to more than one then you need to introduce a linking entity.
Re: Jet SQL: Insert Null FK?
FD, correct.
But that comes back to my argument:
Since the ForeignKey is in the "Pie"-Table, that would (in DB-Terms) make the "Filling"-Table the Master, and the "Pie"-Table the Detail/Slave, and as you said:
There shouldn't be a Detail/Slave-Record without a Master.
So, IMHO, if "Pie" is supposed to be a Master, the cleanest way is the linking entity (as described above), albeit if his scenario (1:1 or 1:0) is correct, it wouldn't be a true "m:m"-relation
Re: Jet SQL: Insert Null FK?
I think our posts might have crossed over (I edited mine several times). What you're saying there is really what I was trying to address when I said that Filling is being used as a categorisation rather than a filling. It's a categorisation because it's the master in the relationship, as you say. Whether it's meant to be a categorisation is up to Dil.