In order to make sure that the database contains high quality data, we ensure data integrity with our data that refers to the consistency and accuracy of data stored. There are different types of data integrity that can be enforced at different levels of solutions. Among these types, we have three types called Domain, Entity and Referential Integrity that are specific to database level for enforcing data integrity.
For enforcing Domain Integrity, SQL Server has given two types of objects called Default and Rule. We have been using these objects for handling Domain Integrity but now it is not recommended to use these for enforcing Domain Integrity.
Let's try to understand what these objects first and see the usage. Default object can be used for creating an object that holds a default value and it can be bound to a column of the table. Rule is same as Default and it creates an object for maintaining rules for columns. See below code as an example.
USE tempdb; GO -- creating default object CREATE DEFAULT CreditLimitDefault AS 10000; GO -- creating a sample table CREATE TABLE dbo.Customer ( CustomerId int PRIMARY KEY , LastName varchar(50) NOT NULL , CreditLimit decimal(16,4) NOT NULL ); GO -- Binding the default to a column -- The object can be bound to many tables EXEC sp_bindefault 'CreditLimitDefault', 'dbo.Customer.CreditLimit'; GO -- creating rule object CREATE RULE CreditLimitRule AS @CreditLimit > 9000; GO -- Binding the rule to a column -- The object can be bound to many tables EXEC sp_bindrule 'CreditLimitRule', 'dbo.Customer.CreditLimit';
As you see, above code creates two objects, CreditLimitDefault and CreditLimitRule that are Default and Rule objects. These objects can be assigned to any column in any table.
As I mentioned above, it is not recommended to use them now as they are deprecated. It is recommended to use Default and Check constraints instead.
Read more on CREATE DEFAULT at: https://msdn.microsoft.com/en-us/library/ms173565.aspx
Read more on CREATE RULE at: https://msdn.microsoft.com/en-us/library/ms188064.aspx