locked
SQL foreign key constraint datetime range RRS feed

  • Question

  • I have two tables with datetime2 column in each and I want to create a foreign key constraint that will validate that the DateTime2_TableA and  DateTime2_TableB are in the same range ( like up to 5 hours difference).

    is there a way to do it?

    ALTER TABLE TableA
    ADD CONSTRAINT FK_TableA_TableB
    FOREIGN KEY (DateTime2_TableA) REFERENCES TableB(DateTime2_TableB);
    Friday, June 19, 2020 3:17 PM

All replies

  • Hi Shauli,

    it is not possible to do something like you want with normal costraints.

    You could create a function that is checking if the date is in the desired range and add the constraint to the table.

    alter table MyTable add constraint chk_CheckFunction check (dbo.fn_constraint_check_function() = 1)

    And the function could look like something like this (of course you have to add the needed logic in the function):

    create function dbo.fn_constraint_check_function()
    returns int
    as begin
        return (select 1)
    end

    Friday, June 19, 2020 3:56 PM
  • If you ever update or delete rows in your parent table (TableB in your example) enforcing the constraint with a check constraint using a function may not work for you.  The check function is only called when you insert or update rows in the child table (TableA in your example).  If you insert a row in TableB and then insert a row in TableA that satisfies the condition that will, of course, be allowed.  But if you now delete the row in TableB, the function is not called.  So the row will be successfully deleted and you will be left with row in the child table (TableA) that does not have a parent in TableB.

    I would enforce this with triggers on both TableA and TableB.  The trigger on TableA should be a Insert,Update trigger and the one on TableB should be an Update,Delete table.  These triggers should check that the desired relationship between the tables is maintained and reject the changes if it is not.

    One thing to consider when writing your triggers is whether you want a row in the child table to able to reference multiple rows in the parent table.  A regular foreign key does not allow this.  So you will want to think about what do you want to if (for example) there are two rows in TableB, one with a time of 10 AM and another with a time of 6 PM.  If you then attempt to insert a row in TableA with a time of 2 PM should that be allowed (there are now 2 matching rows within 5 hours in TableB)?  Whatever your desired result is in this case with affect how you want to code your triggers.

    Tom

    • Proposed as answer by Lily Lii Monday, June 22, 2020 5:38 AM
    Friday, June 19, 2020 5:12 PM
  • Hi Shauli_z,

    CHECKs are not that good for rules that involve other tables, it is used only for basic checks inside one table. 

    Trigger is a special stored procedure which executed automatically on INSERT\UPDATE\DELETE on some table. So you can write a trigger that checks all new rows inserted in tables.

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, June 22, 2020 5:57 AM
  • This is not how a foreign key works. How do you expect to cascade actions in such a model? Since you didn't give us any DDL (please read the forum's netiquette), I'm going to guess that both of these timestamps should be in the same table. This will let you put a constraint on the two columns to guarantee that event number two occurs after event number one and within a certain timeframe.

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Wednesday, June 24, 2020 3:03 AM