CODEWITHSUNDEEP
sql-server
HeWhoStudies
HeWhoStudies

Reputation: 57

Data validation in SQL Server

I'm working on integrating data validation into a table schema in SQL Server 2014.

I want SQL Server to throw an error if a value other then not-applicable (888) or missing (999) is inserted into cigarette if smoking contains 0 (non-smoker). How do I do this?

This is an example of what I have now:

CREATE TABLE [dbo].[CHECK_table]
(
    [study_id] [smallint] NOT NULL,
    [smoke] [smallint] NOT NULL,
    [cigarettes] [smallint] NOT NULL,
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[CHECK_table] WITH CHECK 
    ADD CHECK (([smoke] = (0) OR ([smoke] = (1) OR [smoke] = (8)) OR [smoke] = (9)))
GO

ALTER TABLE [dbo].[CHECK_table] WITH CHECK 
    ADD CHECK (([cigarettes] <= (90) OR [cigarette] = (888) OR [cigarette] = (999)))
GO

Upvotes: 0

Views: 210

Answers (2)

David T. Macknet
David T. Macknet

Reputation: 3170

As expressed in one of the comments, Why not just let them be NULL for unknown data? If you use a nullable bit field, you can get the same amount of detail you'd get by your scheme, with better storage and performance, and no need to enforce any constraints.

My suggestion:

CREATE TABLE #CHECK_table(
  [study_id] [smallint] NOT NULL,
  [smoke] [bit] NULL,
  [cigarettes] [smallint] NULL ,
    check (( case when [smoke] = 0 then 0 else 1 end ) = 1)
   )

insert into #check_table ( study_id, smoke, cigarettes ) select 1, 1, 12
insert into #check_table ( study_id, smoke, cigarettes ) select 1, 0, 12
insert into #check_table ( study_id, smoke, cigarettes ) select 1, 1, null

The second insert there will fail with an error about the insert conflicting with the check constraint.

(aside: And also, why are you using smallint - do you not expect to see very many records?)

add:

For export, when doing your final select from your table (as above), you could do:

select  [study_id],
    cast(case when [smoke] is null then 9 else [smoke] end as smallint) as [smoke],
    [cigarettes]
from    #CHECK_table

That way, you'd have clean data in your tables, without any codification. From a data standpoint, it's much clearer what you're seeing when you look at a bit column - it's either yes, no, or hasn't been answered. This also means that it's clear, when looking through the code, exactly that you've been codifying the data as well as how you've codified it.

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 176214

You could use CASE and replace multiple OR to IN:

CREATE TABLE #CHECK_table(
  [study_id] [smallint] NOT NULL,
  [smoke] [smallint] NOT NULL,
  [cigarettes] [smallint] NOT NULL
);


ALTER TABLE #CHECK_table 
WITH CHECK ADD CHECK ([smoke] IN (0,1,9));

ALTER TABLE #CHECK_table 
WITH CHECK ADD CHECK (((CASE WHEN [smoke] = 0 AND [cigarettes] IN(999,888) THEN 1
                             WHEN [smoke] <> 0 AND ([cigarettes] <= 888 
                                                   OR [cigarettes] = 999) THEN 1
                            ELSE 0 END) = 1)
                     );

LiveDemo

Upvotes: 1

Related Questions