Organization of Databases as well as Tables (Relations) Part – 6

Check Constriction

A Check constriction implements area reliability by means of limiting the data to be added in a column or attribute. It is thinkable to describe several Check constrictions on a one (1) column or attribute. These several Check constrictions are assessed in the sequence in which they have been formed. The code of implementing the Check constriction at the time of forming the table or relation is as follows:

CREATE TABLE My_Table_Name

( My_Column_Name [ CONSTRAINT My_Constraint_Name ] CHECK ( SQL_Expression ) My_Column_Name [ , My_Column_Name [ , My_Column_Name ] ] )

here,

· My_Table_Name – It stipulates the fresh table or relation title that an individual wants to provide. The table or relation title should be under 128 characters.

· My_Column_Name – It is the label of the column or attribute on which the Check constriction is to be demarcated.

· My_Constraint_Name – It is the label of the constriction that an individual is forming as well as this constriction should follow the guidelines intended for the identifier.

· SQL_Expression – It stipulates the settings which describe which sort of checking is to be performed on the column or attribute. SQL_Expression can contain features, like – mathematical calculations, relational operators, and otherwise keywords, like – IN, LIKE, as well as BETWEEN.

A sole Check constriction can be implemented on several columns or attributes as soon as it is demarcated at the table or relation level. The Check constriction can be stated by means of the subsequent keywords:

· IN – In the direction of confirming that the data added are form the group of fixed SQL expression.

· LIKE – In the direction of confirming that the data added in the particular columns or attributes are of definite form. This can be done by means of WILDCARDS expression.

· BETWEEN – In the direction of confirming a definite range of fixed SQL expression by means of the BETWEEN keyword. The maximum as well as the minimum limit of data are also incorporated in the range.

Recommendations For Forming CHECK Constriction

· It can be formed at the column or attribute level.

· It can include end – user – stated search situations.

· It can mention additional columns or attributes of the identical table or relation.

· It cannot cover sub – queries.

· It do not scrutiny the present information in the table or relation when formed using the WITH NOCHECK selection.

Default Constriction

The Default constriction can be castoff to allot a fixed data to a column or attribute, as well as the end – user is not required to add any data for this type of column or attribute. Simply one (1) default constriction can be formed for a column or attribute however the column or attribute cannot be an IDENTITY column or attribute. The system provides data, like – USER, CURRENT_USER as well as end – user – stated data can be allocated by way of Default data. The code of implementing the Default constriction at the time of forming the table or relation is as follows:

CREATE TABLE My_Table_Name

( My_Column_Name [ CONSTRAINT My_Constraint_Name ] DEFAULT

( Default_Expression | NULL ) My_Column_Name [ , My_Column_Name

[ , My_Column_Name ] ] )

here,

· My_Table_Name – It stipulates the fresh table or relation title that an individual wants to provide. The table or relation title should be under 128 characters.

· My_Column_Name – It is the label of the column or attribute on which the Default constriction is to be demarcated.

· My_Constraint_Name – It is the label of the constriction that an individual is forming as well as this constriction should follow the guidelines intended for the identifier.

· SQL_Expression – It stipulates an SQL expression which has data. This can cover a NULL value also.

An individual can practice the DEFAULT database items to form a Default constriction which can be implemented to the columns or attributes over the tables or relations in the similar database. Intended for this, an individual can form database items by means of the CREATE DEFAULT command.

Applying Guidelines

The guideline (rule) confirms area reliability for columns / attributes or end – user stated data type. The guideline (rule) is implemented to the column / attribute or the end – user – stated data type afore of an INSERT or UPDATE command is supplied. In supplementary words, a guideline (rule) stated a limitation on the data of a column / attribute or an end user –stated data types. Guidelines (rules) are castoff to apply corporate – associated limitations or boundaries. A guideline (rule) can be formed by means of the CREATE RULE command. The code of the CREATE RULE command is as follows:

CREATE RULE My_Rule_Name AS My_Conditional_Expression

here,

· My_Rule_Name – It stipulates the title of the fresh guideline (rule) that an individual desires to form as well as it should follow the instructions intended for the identifiers.

· My_Conditional_Expression – It stipulates the situation(s) which describes the guideline (rule). It can be whichever SQL expression which is legal in a WHERE clause as well as can contain features, like – mathematical calculations, relational operators, and otherwise keywords, like – IN, LIKE, as well as BETWEEN.

The variable stated in the conditional SQL expression should be prefix using the ‘ @ ’ character. The SQL expression mentions the data that has been stated using the INSERT as well as UPDATE command. Subsequently after an individual has formed the guideline (rule), the individual should trigger the guideline (rule) by means of a system stored procedure, SP_BINDRULE. The code of SP_BINDRULE is as follows:

SP_BINDRULE < ‘ My_Rule_Name ’ > , < ‘ My_Object_Name ’ > , [ < ‘ FutureOnly_Flag ’ > ]

Where,

· My_Rule_Name – It stipulates the label of the new rule that an individual desires to form to bind.

· My_Object_Name – It stipulates the item label on which an individual desires to bind the rule.

· FutureOnly_Flag – It implements solely when an individual desires to bind the rule to an end – user – stated data type.

As soon as an individual derives to eliminate the rule, the individual can make use of the SP_UNBINDRULE system stored procedure.

Using End – User – Demarcated Types

End – user – demarcated data type are custom data types demarcated by the end – users using a custom label. End – user – demarcated data types permit altering the compound data type castoff in the database. The end – user – demarcated data types are established on the system data types plus can be castoff to pre – describe numerous elements of a column or attribute, like – the data type, length as well as whether it has the provision of NULL data.

An individual can form end – user – demarcated data types by means of the CREATE TYPE command. The code of the CREATE TYPE command is as follows:

CREATE TYPE [ My_Schema_Name . ] My_Type_Name { FROM SQL_Base_Type [ ( Precision [ , Scale ] ) ] [ NULL | NOT NULL] }

Where,

· My_Schema_Name – It stipulates the label of the schema to which the identified data type otherwise the end – user – demarcated data type fits in.

· My_Type_Name – It stipulates the label of the identified data type otherwise the end – user – demarcated data type.

· Base_Type – It stipulates the SQL Server provided data type on which the identified data type is grounded.

· Precision – It stipulates the fraction or numeric point. Fraction as well as numeric is non – negative figures which designate the extreme aggregate number of fraction digit which can be kept, both to left as well as to right of the fraction point.

· Scale – It states the fraction otherwise numeric scale.

· NULL | NOT NULL – It stipulates whether the data type can grasp a null data If not stated NULL is the by default data.

In the upcoming part we will be discussing about how to form a segregated (partitioned) table or relation then how to form a segregated (partitioned) function, how to form a segregated (partitioned) scheme and lastly how to form a table or relation by means of the segregated (partitioned) scheme.