I have a main table Pies and a lookup table Fillings.
The main table has a foreign key, but some rows can have no entry, i.e. Null. I want to be able to insert new Pies rows after populating Fillings, providing the Filling name or Null.
I create the database and add data from a pair of text files:
Fillings.txt
Code:
Apples
Bananas
Cherries
Dates
"Ritz Crackers"
Elderberries
Option Explicit
Private Const MDB_NAME As String = "PieData"
Private Const FILLINGS_NAME As String = "Source Data\Fillings.txt"
Private Const PIES_NAME As String = "Source Data\Pies.txt"
Private Const DB_CONNSTRING As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Engine Type=5;Data Source='$MDB$.mdb'"
Private Sub CreateDB()
Dim Connection As ADODB.Connection
Dim F As Integer
Dim Filling As Variant 'Can be Null (#NULL# in text file).
Dim PieName As String
With CreateObject("ADOX.Catalog")
.Create Replace$(DB_CONNSTRING, "$MDB$", MDB_NAME)
Set Connection = .ActiveConnection
End With
With Connection
.Execute "CREATE TABLE Fillings(" _
& "FillingID IDENTITY CONSTRAINT PK_FillingID PRIMARY KEY," _
& "Filling TEXT(50) WITH COMPRESSION NOT NULL UNIQUE)", _
, adCmdText
'Note that TimeStamp is a reserved word in Jet SQL, so we escape it with
'grave accent (`) not apostrophe ('). You could also use the old SQL-89
'brackets ([ and ]) instead since they are still accepted for backwards
'compatibility:
.Execute "CREATE TABLE Pies(" _
& "PieID IDENTITY CONSTRAINT PK_PieID PRIMARY KEY," _
& "Pie TEXT(50) WITH COMPRESSION NOT NULL," _
& "FillingID INTEGER NULL CONSTRAINT FK_FillingID " _
& "REFERENCES Fillings(FillingID) " _
& "ON UPDATE CASCADE ON DELETE SET NULL," _
& "`TimeStamp` DATETIME NOT NULL)", _
, adCmdText
.Execute "CREATE PROC PiesViewUpdatable AS " _
& "SELECT PieID,Pie,Filling,`TimeStamp` " _
& "FROM Pies LEFT OUTER JOIN Fillings ON Pies.FillingID=Fillings.FillingID " _
& "ORDER BY Pie ASC", _
, adCmdText
.Execute "CREATE PROC PiesViewFormatted AS " _
& "SELECT PieID,Pie,Filling," _
& "FORMAT$(`TimeStamp`,'YYYY-MM-DD HH:NN:SS') AS `TimeStamp` " _
& "FROM Pies LEFT OUTER JOIN Fillings ON Pies.FillingID=Fillings.FillingID " _
& "ORDER BY Pie ASC", _
, adCmdText
.Execute "CREATE PROC InsertFilling(NewFilling TEXT(50)) AS " _
& "INSERT INTO Fillings(Filling)VALUES(NewFilling)", _
, adCmdText
'Note the MAX() hack to return NULL when FillingName does not find a match,
'such as when we pass NULL for FillingName:
.Execute "CREATE PROC InsertPie(NewPie TEXT(50),PieFilling TEXT(50))AS " _
& "INSERT INTO Pies(Pie,FillingID,`TimeStamp`) " _
& "SELECT NewPie,MAX(FillingID),NOW() " _
& "FROM Fillings WHERE Filling=PieFilling", _
, adCmdText
F = FreeFile(0)
Open FILLINGS_NAME For Input As #F
Do Until EOF(F)
Input #F, Filling
.InsertFilling Filling
Loop
Close #F
F = FreeFile(0)
Open PIES_NAME For Input As #F
Do Until EOF(F)
Input #F, PieName, Filling
.InsertPie PieName, Filling
Loop
Close #F
.Close
End With
End Sub
Private Function FileExists(ByVal FileName As String) As Boolean
On Error Resume Next
GetAttr FileName
FileExists = Err.Number = 0
End Function
Private Sub Main()
ChDir App.Path
ChDrive App.Path
If Not FileExists(MDB_NAME & ".mdb") Then CreateDB
End Sub
Works fine:
BTW: VB6.exe Data View stores its Data Links in:
Code:
HKEY_CURRENT_USER\SOFTWARE\VB and VBA Program Settings\Microsoft Visual Basic AddIns\VBDataViewWindow
My question...
In Jet SQL is there a less hackish way of doing this than using MAX()?
Code:
CREATE PROC InsertPie(NewPie TEXT(50), PieFilling TEXT(50)) AS
INSERT INTO Pies (Pie, FillingID, `TimeStamp`)
SELECT NewPie, MAX(FillingID), NOW() FROM Fillings WHERE Filling = PieFilling
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.
Last edited by dilettante; Sep 23rd, 2020 at 01:43 PM.
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.
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.
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
Last edited by Zvoni; Sep 24th, 2020 at 09:42 AM.
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
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.
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.)
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
An obscure body in the SK system. The inhabitants call it Earth
Posts
7,902
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)
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
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.
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
An obscure body in the SK system. The inhabitants call it Earth
Posts
7,902
Re: Jet SQL: Insert Null FK?
You can use the above in a bulk scenario:-
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.
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.
Last edited by FunkyDexter; Sep 25th, 2020 at 03:38 AM.
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
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
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
An obscure body in the SK system. The inhabitants call it Earth
Posts
7,902
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.
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd