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

Primary Key Constriction

The Primary Key constriction is demarcated on a column / attribute or on a group of columns / attributes whose data exclusively classify each and every rows or tuples in a table or relation. These columns or attributes are denoted to be the Primary Key columns or attributes. A Primary Key column or attribute cannot have any NULL data as because it is castoff to exclusively classify the rows or tuples in a table or relation. The Primary Key constriction confirms the object reliability. An individual can describe a Primary Key constriction at the time of forming the table or relation otherwise an individual can implement it in future by means of modifying the table or relation. But if an individual describe the Primary Key constriction afterwards adding rows or tuples, the SQL Server will produce an error message if the rows or tuples has any identical data in the column or attribute. At the time of describing a Primary Key constriction, an individual has to state a label for the constriction. If a label is not stated, the SQL Server will spontaneously allot a label to the constriction.

If a Primary Key constriction is demarcated on a column or attribute which previously has information, at that point the prevailing information in the column or attribute gets checked. If single identical information is present, then the Primary Key constriction is overruled. The code for implementing the Primary Key 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 PRIMARY KEY ]

[ CLUSTERED | NONCLUSTERED ] 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 Primary Key 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.

· CLUSTERED | NONCLUSTERED – This are the keywords which stipulates whether a clustered or else a non – clustered index is to be formed for the Primary Key constriction.

Unique Constriction

The Unique constriction is castoff to apply individuality on non – Primary Key columns or attributes. A Primary Key constriction column or attribute spontaneously contains a limitation for exclusivity. The Unique constriction is alike the Primary Key constriction with the exception of that it permits one (1) NULL row or tuple. Several Unique constrictions can be formed on a table or relation. The code of implementing the Unique 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 UNIQUE ] [ CLUSTERED | NONCLUSTERED ] My_Ccolumn_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 Unique 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.

· CLUSTERED | NONCLUSTERED – This are the keywords which stipulates whether a clustered or else a non – clustered index is to be formed for the Unique constriction.

Foreign Key Constriction

An individual can practice the Foreign Key constriction to eliminate the contradiction in two (2) tables or relations at the time when the information in one (1) table or relation is dependent on the information of a different table or relation. A Foreign Key constriction links one or several column or attribute (the Foreign Key) of a table or relation with a same group of columns or attributes (the Primary Key) in a different table or relation on which a Primary Key constriction has been demarcated. The code of implementing the Foreign Key constriction at the time of forming the table or relation is as follows:

CREATE TABLE My_Table_Name

( My_Column_Name_F [ CONSTRAINT My_Constraint_Name FOREIGN KEY

( My_Column_Name_F [ , My_Column_Name_F ] )

REFERENCES My_Table_Name_P ( My_Column_Name_P

[ , My_Column_Name_P [ , My_Column_Name_P ] ] ) ]

My_Column_Name_F [ , My_Column_Name_F [ , My_Column_Name _F ] ] )

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_F – It is the label of the column or attribute on which the Foreign Key constriction is to be demarcated.

· My_Constraint_Name – It is the label of the constriction that an individual is forming on which the Foreign Key constriction will be demarcated as well as this constriction should follow the guidelines intended for the identifier.

· My_Table_Name_P – It stipulates the title of the linked table or relation in which the Primary Key constriction has been stated.

· My_Column_Name_P – It is the label of the Primary Key column or attribute of the linked table or relation on which the Primary Key constriction is to be demarcated.

In the upcoming part we will be discussing about how to build check constriction, what are the recommendations for forming CHECK constriction, then how to build Default constriction and applying guidelines for the implementation of Default constriction and how to use End – User – Demarcated Types.