What are Rule and Default in SQL?

Rule and default in SQL are cousins of check and default constraints that are something of an old SQL server standby and definitely not without their advantages. The rules and defaults are very natural apart from constraints. Constraints are features of a table but rules and defaults are actual objects. The constraints can be defined on the table level but rules and defaults can be defined independently.

In this article, we will explain the fundamental Rule and Default in SQL cousins of check and default constraints.

Table of Contents

What is Rule?

A rule is very similar to a check constraint but the only difference is that rules work with just one column at a point in time. It can bind the same rule separately with multiple columns in a table. It works independently with each column and is not aware of the other columns at all.

How to Create Rule in SQL?

Refer to the syntax and examples below to create rules –

Syntax:

USE [Database]
GO
CREATE RULE Rule_Name
AS Rule_Condition
GO

Example:

USE [SQLPOST]
GO
CREATE RULE SalaryRule
AS @Salary>0
GO

Result: Refer to the below screenshot as the SalaryRule was created and shown on the left side of the window.

To Inspect the Syntax of an Existing Rule and Default in SQL

Refer to the syntax and examples below to inspect the syntax of an existing rule –

Syntax:

USE [Database]
GO
EXEC sp_helptext Rule_Name
GO

Example:

USE [SQLPOST]
GO
EXEC sp_helptext SalaryRule
GO

Result: Refer to the below screenshot as the SalaryRule was created and shown on the bottom side of the window(Result Set).

How to Associate or Bind an Existing Rule with a Column?

Refer to the syntax and examples below to associate or bind an existing rule with a column –

Syntax:

USE [Database]
GO
EXEC sp_bindrule 'Rule_Name','Table_Name.Column_Name'
GO

Example:

USE [SQLPOST]
GO
EXEC sp_bindrule 'SalaryRule','Employee.Salary'
GO

Result: Refer to the below screenshot as the SalaryRule is bound with the Employee table’s Salary column and shown on the bottom side of the window(Result Set).

To Remove an Existing Rule From a Column

Refer to the syntax and examples below to unbound an existing rule from a column –

Syntax:

USE [Database]
GO
EXEC sp_unbindrule 'Table_Name.Column_Name'
GO

Example:

USE [SQLPOST]
GO
EXEC sp_unbindrule 'Employee.Salary'
GO

Result: Refer to the below screenshot as the SalaryRule is unbounded with the Employee table’s Salary column and shown on the bottom side of the window(Result Set).

To Drop an Existing Rule in SQL

Refer to the syntax and examples below to drop or remove an existing rule –

Syntax:

USE [Database]
GO
DROP RULE Rule_Name
GO

Example:

USE [SQLPOST]
GO
DROP RULE SalaryRule
GO

Result: Refer to the below screenshot as the SalaryRule is removed and shown on the bottom side of the window(Result Set).

What is Default in SQL?

Default is more similar to a default constraint but only the difference is that default works with just one column at a point in time. It can bind the same default separately with multiple columns in a table. It works independently with each column and it is not aware of the other columns at all.

How to Create Default in SQL?

Refer to the syntax and examples below to create defaults –

Syntax:

USE [Database]
GO
CREATE DEFAULT Default_Name
AS 0
GO

Example:

USE [SQLPOST]
GO
CREATE DEFAULT SalaryDefault
AS 0
GO

Result: Refer to the below screenshot as the SalaryDefault is created and shown on the left side of the window.

To Inspect the Syntax of an Existing Rule and Default in SQL

Refer to the syntax and examples below to inspect the syntax of an existing default –

Syntax:

USE [Database]
GO
EXEC sp_helptext Default_Name
GO

Example:

USE [SQLPOST]
GO
EXEC sp_helptext SalaryDefault
GO

Result: Refer to the below screenshot as the SalaryDefault was created and shown on the bottom side of the window(Result Set).

How to Associate an Existing Default with a Column

Refer to the syntax and examples below to associate or bind an existing default with a column –

Syntax:

USE [Database]
GO
EXEC sp_bindefault 'Default_Name','Table_Name.Column_Name'
GO

Example:

USE [SQLPOST]
GO
EXEC sp_bindefault 'SalaryDefault','Employee.Salary'
GO

Result: Refer to the below screenshot as the SalaryDefault is bound with the Employee table’s Salary column and shown on the bottom side of the window(Result Set).

To Remove or Disassociate an Existing Default from a Column

Refer to the syntax and examples below to unbound an existing Default from a column –

Syntax:

USE [Database]
GO
EXEC sp_unbindefault 'Table_Name.Column_Name'
GO

Example:

USE [SQLPOST]
GO
EXEC sp_unbindefault 'Employee.Salary'
GO

Result: Refer to the below screenshot as the SalaryDefault is unbounded with the Employee table’s Salary column and shown on the bottom side of the window(Result Set).

To Drop an Existing Default in SQL

Refer to the syntax and examples below to drop or remove an existing Default –

Syntax:

USE [Database]
GO
DROP DEFAULT Default_Name
GO

Example:

USE [SQLPOST]
GO
DROP DEFAULT SalaryDefault
GO

Result: Refer to the below screenshot as the SalaryDefault is removed and shown on the bottom side of the window(Result Set).

FAQs

What are rules and defaults in the context of databases and programming?

Rules and defaults are mechanisms used to define and enforce data integrity and consistency in databases and programming. They specify conditions and values that data must adhere to

What is a database constraint, and how does it differ from a rule or default?

A database constraint is a specific type of rule that enforces data integrity, such as unique constraints, primary key constraints, and foreign key constraints. Rules and defaults are more general terms that encompass a wider range of conditions and values

How are rules used in programming?

In programming, rules can be implemented as conditional statements or logic that dictate how data is processed or manipulated. They help define the behavior of a program based on certain conditions

What is a default value, and why is it important?

A default value is a pre-defined value that is assigned to a field or variable if no other value is provided. Defaults are important for ensuring that data always has a meaningful or valid value, even if not explicitly set

How can rules and defaults be helpful in data validation?

Rules and defaults help ensure that data entered into a system conforms to specified criteria, reducing the risk of errors and maintaining data quality

Are there any downsides to using rules and defaults?

Overly restrictive rules can make data entry more difficult, while defaults that are set without careful consideration can lead to incorrect data. It’s important to strike a balance

Can rules and defaults be changed or updated after implementation?

Yes, rules and defaults can typically be modified, but changes should be made with caution, as they can impact existing data and system behavior

Are there best practices for defining rules and defaults?

Best practices include clearly documenting rules and defaults, involving stakeholders, and ensuring they align with business requirements and data integrity goals

Conclusion

Nowadays rule and default in SQL are not being used and check and default constraints have taken place as it is an old functionality of SQL. Understanding rules and defaults is essential for anyone dealing with databases. We hope you like this very well.

Please share this on your social media network so that it can be available to even more needy persons, which may benefit them. If you have any questions, feedback, or suggestions regarding this tutorial, please contact us. Do comment right in the comments section below. We will consider your valuable input and try to give you a response ASAP.