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?

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.

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.");

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

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.

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?

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

Cool beans!

Thanks!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.