CODEWITHSUNDEEP
sqlsql-servervalidation
ChrisCa
ChrisCa

Reputation: 11006

SQL Validation on insert - only allow one or another column to be populated

I have a table that allows users to specify a tollerance by an absolute value or by a percentage

e.g. they could say +/- 1,000,000 or 10%

so I have a table with a column for each value

What is th best way to ensure that only one or the other gets specified, not both

I will be adding validation to my UI, but would also like this checked by the database

Some kind of custom constraint?

Upvotes: 2

Views: 2185

Answers (2)

Qqbt
Qqbt

Reputation: 812

This would vary depending on your choice of querying but it comes down to this:

If yourtable.percentage (column1) is not null then you cant set yourtable.value (column2)
you have to validate, check if column value=null and only then insert the value into the other column.

a simple sql command for this:

SELECT CASE <column1> WHEN = NULL      THEN column2
                      when <> NULL     then column1

this is only applicable if you make sure to validate right from the start,the query returns which column to update with your value

Upvotes: 0

gbn
gbn

Reputation: 432210

A CHECK constraint

ALTER TABLE MyTable WITH CHECK ADD
   CONSTRAINT CK_Mytable_ABSorPercent
          CHECK (ABSValue IS NULL OR PercentValue IS NULL)

This assumes your 2 columns are called ABSValue and PercentValue and both nullable. If they are nullable (they should be), change the check to your sentinel (N/A) value

Upvotes: 5

Related Questions