locked
Enforcing constraint but allowing 0 value RRS feed

  • Question

  • Hi everybody,

    In our database we don't use NULLs except for datetime columns. For the optional FK we use 0 (in case of numeric FK or empty string for string PK). This, of course, presents a problem of enforcing the FK constraints when the FK is not required.

    Is there a way to still have constraint enforced but allow 0 instead of NULL to represent absent FK?

    Thanks a lot in advance.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, August 24, 2017 5:45 PM

Answers

  • Hi Naomi,

    You can't do it directly.  A foreign key column must either have NULL or have a value which is a key in the referenced table.  You could do it by adding a computed column to your table and putting the foreign key on that column.

    Create Table dbo.[SampleTest] (SampleID int Primary Key, 
       RelatedSampleID int NOT NULL DEFAULT (0),
       RelatedSampleIDNonZero as NullIf(RelatedSampleID, 0) Persisted Foreign Key References dbo.[SampleTest]); 

    N


    • Edited by Nimish Rao Thursday, August 24, 2017 7:27 PM was an error in the script
    • Marked as answer by Naomi N Thursday, August 24, 2017 9:06 PM
    Thursday, August 24, 2017 7:26 PM

All replies

  • Hi Naomi,

    You can't do it directly.  A foreign key column must either have NULL or have a value which is a key in the referenced table.  You could do it by adding a computed column to your table and putting the foreign key on that column.

    Create Table dbo.[SampleTest] (SampleID int Primary Key, 
       RelatedSampleID int NOT NULL DEFAULT (0),
       RelatedSampleIDNonZero as NullIf(RelatedSampleID, 0) Persisted Foreign Key References dbo.[SampleTest]); 

    N


    • Edited by Nimish Rao Thursday, August 24, 2017 7:27 PM was an error in the script
    • Marked as answer by Naomi N Thursday, August 24, 2017 9:06 PM
    Thursday, August 24, 2017 7:26 PM
  • I tested that approach and it works. We need to make an internal decision about handling FK/PKs going forward. Perhaps we should simply allow NULLs instead of 0s.

    It is a complex question for our team, but I added your suggestion as an alternative.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, August 24, 2017 9:07 PM
  • Naomi,

    Is it a "data warehouse" project?

    The dimensional folks, Kimball Kinnison ... oops I mean Ralph Kimball, famously doesn't like nulls.

    Even for dates.

    Oh, the stories I can tell ...

    Josh

    Friday, August 25, 2017 2:57 AM
  • I tested that approach and it works. We need to make an internal decision about handling FK/PKs going forward. Perhaps we should simply allow NULLs instead of 0s.

    That is certainly the solution I would favour, but I can understand that the impact on the application is considerable. The computed column is a kludge, but it is likely to be cheaper to implement in the short term. (In the long term, kludges always cost.)

    Friday, August 25, 2017 10:38 AM
  • Hi Josh,

    No, this is highly relational database used in Sales (POS) application. The Sales application is written in C++, the supporting applications were originally written in Visual FoxPro (about 20+ years ago). Then the database was ported into SQL Server. Right now we're in the process of re-writing the Visual FoxPro supporting apps into Web based C#/Entity Framework/AngularJs for front-end. I am one of the developers for that project.

    I am not sure about the original rationale for using 0s instead of NULLs (may be because VFP didn't support NULLs long time ago, or something similar), but we ended up with many FKs not enforced.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, August 25, 2017 3:33 PM
  • Ok, looks like for at least one table for which I raised the issue, the decision is to add 0 row into the table and enforce the FKs for all the related tables (about 20 or more).

    The related question I have now. We now want to set values to 0 in all the related tables when the particular role is deleted (the 0 role is always going to be there). The question is - what is the best solution here? Just use a special stored procedure for deletion or add before delete trigger or is there any other simpler option?

    Also, is there a simple way to find all the related tables with their column names? I suspect if this has to be a manual process (trigger or SP) we want the code to be automatic and not list tables/columns manually.

    Thanks.

    BTW, of course they want me to take care of all the Database changes (enforcing all the FKs in 20+ tables). I asked the question - I got the work laid out for me :)


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles





    • Edited by Naomi N Friday, August 25, 2017 9:07 PM
    Friday, August 25, 2017 8:49 PM
  • Ok, looks like for at least one table for which I raised the issue, the decision is to add 0 row into the table and enforce the FKs for all the related tables (about 20 or more).

    Understandable in the short run. But I hope that I never will have to work with that database.

    The related question I have now. We now want to set values to 0 in all the related tables when the particular role is deleted (the 0 role is always going to be there). The question is - what is the best solution here? Just use a special stored procedure for deletion or add before delete trigger or is there any other simpler option?

    You could set a default of 0 for these columns and then define the FKs as ON DELETE SET DEFAULT.

    Friday, August 25, 2017 9:09 PM
  • Great, at least I don't have to worry about the last part - they all have set 0 as default. I now only need to implement FK for all these tables. Is there a script to automate the process (it will need to be done in our DB project, so probably will have to do it manually anyway).

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, August 25, 2017 9:17 PM
  • I am not sure about the original rationale for using 0s instead of NULLs (may be because VFP didn't support NULLs long time ago, or something similar), but we ended up with many FKs not enforced.

    What can I say but LOL.  Maybe the original original was in dBaseII on floppy disks ... or carved in smooth stones.

    C# supports nulls a lot better than C++ ever did, might be time to go modern.

    Josh

    Friday, August 25, 2017 9:57 PM
  • Great, at least I don't have to worry about the last part - they all have set 0 as default. I now only need to implement FK for all these tables. Is there a script to automate the process (it will need to be done in our DB project, so probably will have to do it manually anyway).

    I guess it can be automated, by running a query over various catalog views, but I since I don't know how the current situation, I can't offer any. If there are no FKs at all, I can't but see that there has to be some manual work, but you could store the table names in a table and then generate the ALTER TABLE ADD CONSTRAINT commands from that table.

    Saturday, August 26, 2017 8:08 AM
  • The database project doesn't seem to allow to specify actions on DELETE and UPDATE for the keys. I'm not sure what is the correct T-SQL code.

    When I try to change it in design view I see no way to add ON UPDATE/ON DELETE behavior. This is the currently generated code - how should I fix it?

    CONSTRAINT [FK_Accounts_AcctType] FOREIGN KEY ([AcctTypeId]) REFERENCES [dbo].[Accttype] ([AcctTypeId]) NOT FOR REPLICATION,
    	CONSTRAINT [FK_Accounts_sec_role] FOREIGN KEY ([role_no]) REFERENCES [dbo].[sec_role] ([role_no]) NOT FOR REPLICATION
    );
    
    GO
    ALTER TABLE [dbo].[accounts] NOCHECK CONSTRAINT [FK_Accounts_AcctType];
    
    go

    UPDATE. Never mind, looks like the syntax should be

    CONSTRAINT [FK_Accounts_sec_role] FOREIGN KEY ([role_no]) 	
    	REFERENCES [dbo].[sec_role] ([role_no]) 
    	ON DELETE SET DEFAULT   
        ON UPDATE CASCADE
    	NOT FOR REPLICATION


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    • Edited by Naomi N Monday, August 28, 2017 6:02 PM
    Monday, August 28, 2017 5:56 PM
  • Looks like nothing is easy in DB project :( Now I am getting this error

    Severity Code Description Project File Line Suppression State
    Error SQL72014: .Net SqlClient Data Provider: Msg 1785, Level 16, State 0, Line 1 Introducing FOREIGN KEY constraint 'FK_Accounts_sec_role' on table 'accounts' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. SiriuswareDB D:\_Siriusware\Main\SiriuswareDB\SiriuswareDB\bin\Release\SiriuswareDB.sql 4480


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, August 28, 2017 6:11 PM
  • Even if I only specify ON DELETE SET DEFAULT I still get the same error when I try to deploy the solution.

    Is there any workaround?

    What should I do?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, August 28, 2017 6:29 PM
  • It doesn't seem to work. First of all, I'm unable to add such constraint at all.

    However, I tested in another table. This table has column SeriesID with default value set to 0. It also has the FK constraint with ON DELETE SET DEFAULT option. That constraint was disabled. I just enabled a constraint, added new row into rsSeries table, updated a few rows in Items table with that new ID. Then I tried to delete a row from the rsSeries table and I got

    Msg 547, Level 16, State 0, Line 9
    The DELETE statement conflicted with the FOREIGN KEY constraint "FK_items_rsSeries_SeriesID". The conflict occurred in database "Siriusware", table "dbo.rsSeries", column 'SeriesID'.
    The statement has been terminated.

    instead of setting the values of SeriesID to 0 in the items table. So, does that SET DEFAULT option work at all?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    • Edited by Naomi N Monday, August 28, 2017 7:20 PM
    Monday, August 28, 2017 7:19 PM
  • Re-viewing this thread again as I wanted to add FK to another table, but again we're using 0 in the absence of the key.

    Presented this again as a question for discussion - I don't think there is a solution for us here :(


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, October 3, 2017 9:25 PM
  • So, I am raising this issue now again and again I am being suggested to add that 0 row into yet another table :(

    Would be nice to have an option of filtered FK constraints... (would not help us as we're supporting SQL 2008 and up, I think).


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    • Edited by Naomi N Tuesday, October 3, 2017 9:46 PM
    Tuesday, October 3, 2017 9:44 PM
  • BTW, do I understand correctly that I can not specify action for constraint with inline syntax, e.g.

    ,CONSTRAINT [FK_i_tmplat_i_tmpnam] FOREIGN KEY ([tmpnam_id]) REFERENCES [dbo].[i_tmpnam] ([tmpnam_id] ON DELETE CASCADE)

    I need to use this syntax instead?

    ALTER TABLE [dbo].[OrderDetail]  WITH CHECK 
    ADD CONSTRAINT [FK_OrderDetail_Order] FOREIGN KEY([OrderID])
    REFERENCES [dbo].[Order] ([OrderID])
    ON DELETE CASCADE


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, October 3, 2017 10:11 PM
  • BTW, do I understand correctly that I can not specify action for constraint with inline syntax, e.g.

    ,CONSTRAINT [FK_i_tmplat_i_tmpnam] FOREIGN KEY ([tmpnam_id]) REFERENCES [dbo].[i_tmpnam] ([tmpnam_id] ON DELETE CASCADE)

    I need to use this syntax instead?

    ALTER TABLE [dbo].[OrderDetail]  WITH CHECK 
    ADD CONSTRAINT [FK_OrderDetail_Order] FOREIGN KEY([OrderID])
    REFERENCES [dbo].[Order] ([OrderID])
    ON DELETE CASCADE


    This works for me:

    CREATE TABLE alpha (a int NOT NULL CONSTRAINT pk_a PRIMARY KEY (a))
    go
    CREATE TABLE beta (b int NOT NULL CONSTRAINT pk_b PRIMARY KEY (b),    
                       a int NULL,
                       CONSTRAINT fk_a_b FOREIGN KEY (a) REFERENCES alpha(a) ON DELETE CASCADE
    )
    go
    DROP TABLE beta, alpha
    


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, October 4, 2017 7:21 AM
  • There was a typo in my code that I didn't realize at that time. I can adjust the code back to inline or leave as a separate statement. I think I'll adjust to be inline.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, October 4, 2017 11:37 AM