Sql Server 2005 ensure columns sum within a table
how can i ensure the total sum of 5 columns within the same table equals 100 during insert or update in SQL Server 2005?
Software/Hardware used:
SQL Server 2005
Software/Hardware used:
SQL Server 2005
ASKED:
March 26, 2010 11:47 PM
UPDATED:
March 29, 2010 12:12 PM
Answer Wiki:
If all records already in the table comply with that restriction, one option would be adding a <a href="http://msdn.microsoft.com/en-us/library/ms188258(SQL.90).aspx">CHECK constraint</a> to the table.
Something like this:
<pre>ALTER TABLE yourTable ADD CONSTRAINT ck_yourConstraint
CHECK (col1+col2+col3+col4+col5=100);</pre>
If NULLs are allowed on those columns, you might want to do something like this:
<pre>ALTER TABLE yourTable ADD CONSTRAINT ck_yourConstraint
CHECK (ISNULL(col1,0)+ISNULL(col2,0)+ISNULL(col3,0)+ISNULL(col4,0)+ISNULL(col5,0)=100);</pre>
-CarlosDL
----------------------
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
