Most of the articles that I presented so far for database development have been centered around the exact Microsoft T-SQL syntax to perform a certain operation. At the University of Rhode Island, I learnt how to program in Pascal before typing in my first ANSI SQL statement. In short, I will always be some type of developer in my heart.
However, many people do not come from a computer science and math background. The SQL Server Management Studio (SSMS) has menus and dialog boxes to achieve many of the same results.
Today, I am going to continue the series of articles that will recreate the [AUTOS] sample database while demonstrating how common T-SQL tasks in my script can be done with SSMS. I want to explore how to ADD, ALTER and DELETE columns using the SSMS graphical interface. Please see section 1I in the code for the table and column definitions.
ALTER TABLE – “Modifies a table definition by altering, adding, or dropping columns and constraints, reassigning partitions, or disabling or enabling constraints and triggers” – MSDN quote.
We will be focusing on making the [DBO].[MAKES] table look like our data model. Right click the [AUTOS] database and choose New Table.
Add [MAKER_ID] as the first surrogate key field and the [MAKER_NM] as second natural key field and the two year fields ([START_YR] & [END_YR]) as regular integers [INT].
I will be glossing over these steps since they were first introduce when I talked about Table Design with SSMS. In short, SSMS is executing a ALTER TABLE ADD COLUMN command in the background.
Please note that we could have defined default constraints or values within the table designer. If we had a computed column, we could have defined it within the column properties. However, there is no expression builder to make this action easier than coding on the fly.
After recreating both tables and constraints, we find out that the business line wants the [MAKER_ID] to be stored as big integer [BIGINT].
How can we make this happen?
Find the [MAKER_ID] column in the object explorer under the table name. Right click and select the modify column menu option.
Find the [MAKER_ID] column in the table designer and change the data type to [BIGINT]. A warning message box will come up stating that [MAKER_ID] is part of a foreign key relationship and will need to be change in the parent table.
Click the save toolbar button to commit the change. A error message box will show up stating that the change could note be made due to the fact that the tables contain data and participate in a relationship.
I have always had issues when there are dependent objects defined on a table column that I want to modify. Therefore, it is very important to get a table design correct the first time. Another solution is to create a duplicate table under a different schema and move the data from one schema to another. Then, break all the relationships, delete the original table, move the table to the old schema and recreate the relationships. As you can see, this is a-lot of work.
In a nutshell, SSMS is trying to execute a ALTER TABLE ALTER COLUMN command in the background.
Let’s try deleting the [END_YR] column from the [MAKES] table. Again, find the column under the table node in the object explorer. Right click and select the delete action from the menu.
The delete object dialog box comes up showing that you want to remove the column. Click OKAY to continue the action and CANCEL to abort.
A error message stating that a CHECK CONSTRAINT is defined on the column. Therefore, the column can not be dropped.
Find the constraint named CHK_END_YR in the object explorer. This will be located under the [DBO].[MAKES] table. Right click and select the delete action from the menu.
The delete object dialog box comes up showing that you want to remove the column. Click OKAY to continue the action. This action will complete successfully.
Repeat the process to delete the [END_YR] column. Confirm the delete action in the dialog box. This action will complete successfully. Another look at the [DBO].[MAKES] table shows the column not longer exists. To recap, SSMS is trying to executing a ALTER TABLE DROP COLUMN command in the background.
In summary, we can perform many of the Data Definition Language (DDL) tasks with the SQL Server Management Studio (SSMS) graphical interface. I just went over the ADD COLUMN, ALTER COLUMN, and DROP COLUMN clauses of the ALTER TABLE statement. These actions were done using SSMS. It is important to note that object dependencies can cause you trouble when altering or deleting columns.
Next time, I will be talking about Crafting Constraints with SSMS.
