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

Altering A Table Or Relation

An individual want to alter tables or relations as soon as there is a necessity to introduce a fresh column or attribute, modify the data type of a column or attribute, or introduce otherwise eliminate any constrictions on the present columns or attributes. For an instance, the XYZBank stocks the loan approval details of every loan in the Management . LoanPending table or relation. Conferring to condition, an individual must introduce additional column or attribute entitled as ApprovalBy in the table or relation to stock the name of the loan manager who have permitted the loan to the customer. In the direction of applying this alteration, an individual can practice the ALTER TABLE command. The code of the ALTER TABLE command is given as follows:

ALTER TABLE [ My_Database_Name . [ My_Scheme_Name ] . ] My_Table_Name

{ ALTER COLUMN My_Column_Name { [ NULL | NOT NULL ] } | [ WITH { CHECK | NOCHECK } ] ADD COLUMN < My_Column_Definition > { ADD CONSTRAINT My_Constraint_Name My_Constraint_Type }

Where,

· My_Database_Name – It stipulates the title of the database in which the table or relation is present.

· My_Schema_Name – It stipulates the label of the schema to which the table or relation is present.

· My_Table_Name – It stipulates the title of the table or relation which is to be changed. When the particular table or relation is not present in the current database, at that time the end – user is necessities to stipulate the database title as well as the schema label manually.

· ALTER COLUMN – It stipulates the label of the changed column or attribute.

· ADD COLUMN – It stipulates the label of the column or attribute to be introduced.

· WITH { CHECK | NOCHECK } – It stipulates whether the present information is to be tested for a recently introduced constriction otherwise it is a re – enabled constriction.

· 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.

· My_Constraint_Type – It is the category of the constriction that is to implemented.

The subsequent SQL query introduces a column or attributes known as ApprovalBy to the Management . LoanPending table or relation:

ALTER TABLE Management . LoanPending

ADD ApprovedBy VARCHAR (30) NOT NULL

In the previous instance, the ApprovedBy column or attribute is introduced and which can hold only string data only.

At the time of altering a table or relation, an individual can delete any constriction the minute it is not necessary any more. An individual can do the job by means of changing the table or relation through using the ALTER TABLE command. The code to delete a constriction is as follows:

ALTER TABLE [ My_Database_Name . [ My_Scheme_Name ] . ] My_Table_Name

DROP CONSTRAINT My_Constraint_Name

Where,

· My_Database_Name – It stipulates the title of the database in which the table or relation is present.

· My_Schema_Name – It stipulates the label of the schema to which the table or relation is present.

· My_Table_Name – It stipulates the title of the table or relation which has the constriction that is to be deleted. When the particular table or relation is not present in the current database, at that time the end – user is necessities to stipulate the database title as well as the schema label manually.

· My_Constraint_Name – It stipulates the label of the constriction that is to be deleted.

Every constriction demarcated on a table or relation is deleted spontaneously as soon as the table or relation is deleted. If the column or attribute, to which an individual is demanding to introduce a constriction has a number of information in it, then the individual can practice WITH CHECK | NOCHECK to stipulate whether the present information will be tested with the introduced constriction.

Giving Another Name To A Table Or Relation

An individual can give new name to a table or relation on every occasion when it is needed. The SP_RENAME system stored procedure is castoff to give another name to a table or relation. SP_RENAME can be castoff to give a new name to whichever database items, like -a table or relation, function, view, trigger, or stored procedure. The code of the SP_RENAME stored procedure is as follows:

SP_RENAME My_Old_Name , My_New_Name

here,

· My_Old_Name – It is the existing label of the database item that an individual want to change.

· My_New_Name – It is the fresh label of the database item that an individual want to provide

An individual can rename a database table or relation in the Object Explorer windows by means of right – clicking the Tables folder under the Database Folder in addition to choosing the Rename choice from the shortcut menu.

Subsequently after a table or relation is formed, an individual might want to understand the particulars of the table or relation. Particulars of the table or relation contain the column / attribute names as well as the constriction. Intended for this tenacity, an individual can practice the SP_HELP statement. The code of the SP_HELP statement is as follows:

SP_HELP [ My_Table_Name ]

Deleting A Table Or Relation

From time to time, the minute a table or relation is not needed, an individual want to remove a table or relation. A table or relation can be removed along with every linked database items, like – index, permission, statistics, view, triggers, rule, as well as the constrictions. An individual can remove a table or relation by means of DROP TABLE command. The code of the DROP TABLE command is as follows:

DROP TABLE [ My_Database_Name . [ My_Scheme_Name ] . ] My_Table_Name

Where,

· My_Database_Name – It stipulates the title of the database in which the table or relation is present.

· My_Schema_Name – It stipulates the label of the schema to which the table or relation is present.

· My_Table_Name – It stipulates the title of the table or relation that an individual wants to remove from the current database. When the particular table or relation is not present in the current database, at that time the end – user is necessities to stipulate the database title as well as the schema label manually.

As soon as a table or relation is removed supplementary database items referenced through the table or relation requires to be removed obviously. This must be completed before removing the table or relation this is for the reason that at the time of removing a table or relation, if defilements happen in the rule of referential reliability then a mistake takes place which stops an individual from removing the table or relation. For that reason, if the individual’s table or relation is referenced at that time the individual should remove the referenced table or relation or else the referenced constriction and at that point the individual should remove the main table or relation.

An individual can delete a database table or relation in the Object Explorer windows by means of right – clicking the Tables folder under the Database Folder in addition to choosing the Delete choice from the shortcut menu.

This is the last part of the article; hope the readers have learned some facts from it.

Thank you.