Enforcing Data Rules

[zachattack05]

You guys are always so helpful, this is like my "go-to" forum for bizarre questions :P

Not sure if this should be enforced by the SQL server or the Application itself.

If we have a table with 2 columns of type int, ColumnA and ColumnB, is there a way to make a rule that says ColumnB must be >= (greater than or equal to) ColumnA?

Is that something that should be enforced SQL side (if even possible) or application side?

[nmaillet]

You should always try and enforce data integrity at the source. This allows for multiple applications to access the data, without each needing to be coded individually (error prone). And a small bug in the application will not cause the data to become invalid. That said, you can add a CHECK constraint to the table:

ALTER TABLE TheTable
ADD CONSTRAINT chk_TheTable CHECK (ColumnA >= ColumnB)

Something like this will work for Microsoft's SQL Server, I can't say for certain about other servers.

[Mitja Bonca]

It can be possible in both side. Which do you prefer.
nmaillet just showed how it will be look like a stored procedure.

If you wanna do in code (C#) you can do it:

int a = int.Parse(textBox1.Text); //but do better checked to make sure if user does not enter a number!
int b = int.Parse(textBox2.Text);
if(b>=a)
{
     //do the insert code
}
else
    MessageBox.Show("Value B must be greater or at least equal to value A.");

[zachattack05]

I'm assuming the check constraint will throw an exception and not insert anything?

[nmaillet]

Yes it will, it is similar to a primary key or not null constraint. It will also throw an exception if you attempt to change the values after it is created.

[zachattack05]

Wait...so if I set ColumnA to 10 and ColumnB to 4 and then later want to change ColumnB to 6...it will throw an exception? Or are you saying it throws the exception on updates if the check constraint is violated?

[nmaillet]

Sorry, yes it will only throw an exception if there is a constraint violation, whether updating values or inserting rows.

[zachattack05]

Cool beans!

Thanks!