Schema change statements are used to make the alteration to the existing database. The schema change statements are defined using the structured query language i.e. SQL. It must be taken care that whatever changes you made to the schema of an existing database must not affect the existing database. In the section ahead, we will discuss more schema change and schema change statements in SQL.
What is Schema Change?
Schema change is a procedure of altering or modifying the existing database objects with the help of some commands. We refer to these commands as schema change statements which are defined using an SQL query language. These commands are also referred to as schema evolution commands.
The schema change statements include adding or dropping tables, attributes, and constraints over the database elements which makes changes in the structure of an existing database. Though it is tough to make changes in the operational database it is necessary to design a good database from the performance and security point of view. These changes in the database must be done carefully.
Why Schema Change is Required?
Schema change is required for maintaining or upgrading the existing logical structure of a database. It is also essential when your application needs an extension or when the application supports the user level modelling where the application is customized according to the need of the user.
Once the application is delivered on the field for the user’s use, in very little time the user creates information in a huge quantity. So, while changing the schema of an object in the database, the field application engineer must take care that all the existing user databases must not get affected while changing the schema of the operational database.
When we talk of schema change what do we exactly alter or modify? With schema change statements we can alter the ‘definition of the class’ by adding or dropping an attribute to or from the class, change the name or type of the attribute, we can even reset the default value of the attribute.
Changing the definition of a class also includes changing the behaviour of the class. The behaviour of the class is defined with the methods it includes. So, to change the behaviour of a class we can add or remove methods to or from the class. Apart from this, we can change the name and behaviour of that method.
Apart from changing the definition of a class by altering its attributes and methods we can also perform changes in the inheritance of the class. We can add a new parent class for the given class or we can remove a parent class. You can also reorder the levels of parent classes for a class.
Instead of changing the definition or behaviour of the class, you can make changes in the existence of the class i.e., you can completely remove a class from the database schema, or you can change the name of an already existing class you can extend the database by adding more classes to it.
Schema Changing Statements
1. Drop Command
Drop command is used to drop or delete the named elements of database schema such as tables, domains or constraints on these elements or the entire schema itself. The drop command can be implemented in two possible ways i.e. using CASCADE and RESTRICT.
To drop a schema in a database even if the schema retains some of its elements then using CASCADE along with the DROP command you can delete the entire schema from the database. For example, if you want to remove the schema EDUCATION and all the tables related to this schema, domains and other elements associated we would execute the following command.
DROP SCHEMA EDUCATION CASCADE
Instead of CASCADE, if you use RESTRICT then the EDUCATION schema will only be dropped if the schema does not have retained a single element in it. To execute the above DROP command with RESTRICT option you first have to drop each element of the EDUCATION schema individually and at last, you can drop the Education schema itself.
Now if you want to drop a particular relation within the EDUCATION schema such as if we no longer want to keep track of the guardians of the students in the EDUCATION database then to drop the GUARDIAN relation we would use the following command.
DROP TABLE GUARDIAN CASCADE
The above command would drop the table and also the other elements referencing the table being dropped such as views constraints etc. If we use the RESTRICTION option instead of CASCADE along with the DROP TABLE command then the table would have been dropped only it doesn’t have any reference constraint associated with it.
The DROP TABLE command alone will drop the entire table along with the records within the table. But if you don’t want to delete the definition of the table but want to delete all the records of the table then you can use the DELETE TABLE command.
2. Alter Command
We also use alter command to change the schema of a database. Using Alter command, you can change the logical structure of a relation (table) in the database. Changing the definition of a table includes adding a new column in the table, modifying the type of attribute (column), deleting the column from the table.
Such as if we want to keep track of the course that students are opting we have to add a course attribute to STUDENT relation in the EDUCATION schema. And this can be done with the command below:
ALTER TABLE EDUCATION.STUDENT ADD COLUMN COURSE VARCHAR (12)
After adding the course attribute to the student relation, a value for the course attribute must be added for each individual student tuple. You can add attribute value using the UPDATE command or else you must specify the default clause. If not all the tuples would have a NULL value for the new attribute.
As you have added a new column using ALTER command you can even remove a column using DROP along with the ALTER. For example, let us drop the guardian column of the student relation in the Education schema.
ALTER TABLE EDUCATION.STUDENT DROP COLUMN Guardian CASCADE;
As we have seen that DROP command comes with two options CASCADE and RESTRICT. Using CASCADE removes all the constraints and views referencing the dropped column. Instead, if we use RESTRICT option the column is removed only if no constraints and views are referencing the dropped column.
You can even alter the data type of the existing column in relation using MODIFY command along with the ALTER.
ALTER TABLE STUDENT MODIFY COLUMN Address CHAR(100);
To change the name of the column we can use RENAME command along with the ALTER command.
ALTER TABLE STUDENT RENAME COLUMN NAME To STUDENT_NAME
Well, you have several kinds of approaches for schema change such as programmed schema change, manual schema change, you can even perform schema change in background mode or lazy evolution mode. With the background mode, the changes are performed over a long time. With the lazy evolution mode, the schema elements are not updated until they are accessed and found to be inconsistent.
The schema change can be performed statically or dynamically. Static schema change involves updating the schema outside the application processing whereas the dynamic schema change can be done in the application that requires user-definable types.
So ending up here we conclude that schema change is an essential part of a database system that improve the logical structure of the database and enhance its performance.