Difference between Constraints and Rules in SQL

Question

What is the difference between using constraints and rules in SQL, preferably SQL server 2008 R2. Can anyone explain and demonstrate with a real time example

Conn

Don't forget that rules are obsolete - see create rule in Sql Server 2012. – Wedekind

Still unclear to you? – Outwork

@NikolaMarkovinović - although there is a 'may remove' warning from 2017, it's in SQL2019 so it is still available in new current versions of MySQL. – Theorem

Answer 1

There are a lot of difference but One the most notable difference is that we can bind rules to a datatypes whereas constraints are bound only to columns. this feature enable us to create our own data-type with the help of Rules and get the input according to that.

I have found a good example, check the following link. constraints vs rules

Sumach

Answer 2

From Postgres manual:

The PostgreSQL rule system allows one to define an alternate action to be performed on insertions, updates, or deletions in database tables. Roughly speaking, a rule causes additional commands to be executed when a given command on a given table is executed

A constraint, as an example, can be used to ensure that a given number never matches some value. Such a birth year should not be under 1880 for new users registering or above the current year.

Acred

Answer 3

I would add some differences

1) rules are reusables , so one time you make a rule you can be bound to one or more columns. 2) in the other hand, Constrains : - single column can have several constrainst. - message can be bound to constraints constrains are normaly created in the "create table statmen" or with a "alter table add constraint "

cheers

Delapaz

Answer 4

In short:

Rules is a feature that executes some validation functions based on some other data or systems.

CHECK constraint is used to restrict the values in a column to allow only if it meets the condition based on this particular value.

See this article for possible helpful information:

Difference between Constraints and Rules in SQL

Outright