Results 1 to 14 of 14

Thread: Jet SQL: Insert Null FK?

  1. #1

    Thread Starter
    PowerPoster
    Join Date
    Feb 2006
    Posts
    21,566

    Question Jet SQL: Insert Null FK?

    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
    Pies.txt
    Code:
    "Big John's Deluxe Crumble",Apples
    Apple,Apples
    "Tartness Extravaganza",Cherries
    "Cream Dream",Bananas
    "Basic Fruity",Elderberries
    Cherry,Cherries
    "Basic Banana Cream",Bananas
    "Hollow Crust".#NULL#
    "Mock Apple","Ritz Crackers"
    "Minced Mania",Dates
    "George Washington",Cherries
    Here is the code:
    Code:
    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:

    Name:  sshot.png
Views: 71
Size:  11.0 KB

    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
    Attached Files Attached Files

  2. #2
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    33,021

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    PowerPoster
    Join Date
    Feb 2006
    Posts
    21,566

    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.
    Last edited by dilettante; Sep 23rd, 2020 at 01:43 PM.

  4. #4
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    33,021

    Re: Jet SQL: Insert Null FK?

    Quote Originally Posted by dilettante View Post
    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...

    hmmm.... I see your point...

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5

    Thread Starter
    PowerPoster
    Join Date
    Feb 2006
    Posts
    21,566

    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.

  6. #6
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,269

    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
    Last edited by Zvoni; Sep 24th, 2020 at 09:42 AM.
    One System to rule them all, One IDE to find them,
    One Code 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.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  7. #7

    Thread Starter
    PowerPoster
    Join Date
    Feb 2006
    Posts
    21,566

    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.

  8. #8

    Thread Starter
    PowerPoster
    Join Date
    Feb 2006
    Posts
    21,566

    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.

  9. #9
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,269

    Re: Jet SQL: Insert Null FK?

    Quote Originally Posted by dilettante View Post
    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.)
    One System to rule them all, One IDE to find them,
    One Code 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.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  10. #10
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,630

    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)
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  11. #11
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,269

    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.
    One System to rule them all, One IDE to find them,
    One Code 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.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  12. #12
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,630

    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.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  13. #13
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,269

    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
    One System to rule them all, One IDE to find them,
    One Code 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.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  14. #14
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,630

    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.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width