Altering and Dropping Databases And Tables In Microsoft SQL Server 2000, Part 3 - ALTER TABLE
(Page 3 of 3 )
The ALTER TABLE command modifies a table definition by altering, adding, or dropping columns and constraints or by disabling or enabling constraints and triggers. Here's the syntax:
ALTER TABLE
table
{ [ ALTER COLUMN column_name{ new_data_type [ ( precision [ , scale ] ) ][
COLLATE < collation_name > ][ NULL | NOT NULL ]| {ADD | DROP } ROWGUIDCOL }]|
ADD{ [ < column_definition > ]| column_name AS computed_column_expression} [
,...n ]| [ WITH CHECK | WITH NOCHECK ] ADD{ < table_constraint > } [ ,...n ]|
DROP{ [ CONSTRAINT ] constraint_name| COLUMN column } [ ,...n ]| { [ WITH CHECK
| WITH NOCHECK ] CHECK | NOCHECK } CONSTRAINT{ ALL | constraint_name [ ,...n ] }
| { ENABLE | DISABLE } TRIGGER{ ALL | trigger_name [ ,...n ] }}<
column_definition > ::={ column_name data_type }[ [ DEFAULT constant_expression
] [ WITH VALUES ]| [ IDENTITY [ (seed , increment ) [ NOT FOR REPLICATION ] ] ]]
[ ROWGUIDCOL ][ COLLATE < collation_name > ][ < column_constraint > ] [ ...n ]<
column_constraint > ::=[ CONSTRAINT constraint_name ]{ [ NULL | NOT NULL ]| [ {
PRIMARY KEY | UNIQUE }[ CLUSTERED | NONCLUSTERED ][ WITH FILLFACTOR = fillfactor
][ ON { filegroup | DEFAULT } ]]| [ [ FOREIGN KEY ]REFERENCES ref_table [ (
ref_column ) ][ ON DELETE { CASCADE | NO ACTION } ][ ON UPDATE { CASCADE | NO
ACTION } ][ NOT FOR REPLICATION ]]| CHECK [ NOT FOR REPLICATION ](
logical_expression )}< table_constraint > ::=[ CONSTRAINT constraint_name ]{ [ {
PRIMARY KEY | UNIQUE }[ CLUSTERED | NONCLUSTERED ]{ ( column [ ,...n ] ) }[ WITH
FILLFACTOR = fillfactor ][ ON {filegroup | DEFAULT } ]]| FOREIGN KEY[ ( column [
,...n ] ) ]REFERENCES ref_table [ ( ref_column [ ,...n ] ) ][ ON DELETE {
CASCADE | NO ACTION } ][ ON UPDATE { CASCADE | NO ACTION } ][ NOT FOR
REPLICATION ]| DEFAULT constant_expression[ FOR column ] [ WITH VALUES ]| CHECK
[ NOT FOR REPLICATION ]( search_conditions )}
As with the CREATE command here is quite a handful to deal with, we will look at it with examples to give you the feel of it. Before that, let us take a look at the table Employees, which we created the second part of the tutorial, using the syntax below.
EXEC sp_help Employees
For the sake of convenience we are going to edit the result and focus only on the parts which matter to us, actually you will see a lot more to this.
| Column_Name | Type | Computed | Length | Prec | Scale | Nullable |
| EmployeeId | int | No | 4 | 10 | 0 | No |
| EmployeeName | varchar | No | 30 | | | No |
| Address1 | varchar | No | 25 | | | No |
| Address2 | varchar | No | 20 | | | No |
| City | varchar | No | 20 | | | No |
| State | varchar | No | 2 | | | No |
| Zip | varchar | No | 10 | | | No |
| Contact | varchar | No | 25 | | | No |
| Phone | char | No | 15 | | | No |
| Salary | Money | No | 8 | 19 | 4 | No |
| HireDate | smalldatetime | No | 4 | | | No |
| TerminationDate | smalldatetime | No | 4 | | | yes |
| Department | varchar | no | 25 | | | No |
Let’s say we decide to add in the social security number for all employees and keep track of all the previous employer information on our employees. It just involves adding two columns, isn’t it simple?
ALTER TABLE Employees Add SSN
varchar
(11) NULL, PreviousEmployer varchar(30) NULL;
Back in versions 6.5 we weren’t allowed to add columns with NOTNULL option after the table has been created. Beginning with Version 7, we gained to ability to add NOTNULL columns, with one catch, we have to provide a default value for that column. The default is then used to populate the new columns for any row that is already in the table. Let us look at an example to get a clear picture.
ALTER TABLE Employees AddDateOfBirth
datetime NULL
,LastRaiseDate datetime
NOTNULL DEFAULT ‘2001-01-01’;[CODE]
The output from
the above two syntax is TABLE ALTERED, to make sure that the changes are
effective use:
[CODE]EXEC sp_help Employees
| Column_Name | Type | Computed | Length | Prec | Scale | Nullable |
| EmployeeId | int | No | 4 | 10 | 0 | No |
| EmployeeName | varchar | No | 30 | | | No |
| Address1 | varchar | No | 25 | | | No |
| Address2 | varchar | No | 20 | | | No |
| City | varchar | No | 20 | | | No |
| State | varchar | No | 2 | | | No |
| Zip | varchar | No | 10 | | | No |
| Contact | varchar | No | 25 | | | No |
| Phone | char | No | 15 | | | No |
| Salary | Money | No | 8 | 19 | 4 | No |
| HireDate | smalldatetime | No | 4 | | | No |
| TerminationDate | smalldatetime | No | 4 | | | yes |
| Department | varchar | No | 25 | | | No |
| SSN | Varchar | No | 11 | | | Yes |
| PreviousEmployer | Varchar | No | 30 | | | No |
| DateOfBirth | Datetime | No | 8 | | | yes |
| LastRaiseDate | Datetime | no | 8 | | | No |
As you can see, all our columns have been added. But the thing to note, however, is that they all went to the end of the column list. There is no way to add a column to a specific location in the SQL Server. If you want to add a column in the middle say you want the employee’s SSN after EmployeeName. Then you need to create a new table (with a different name), copy the data over to the new table, DROP the existing table and then rename the new one. {mospagebreak title=DROP TABLE} Performing a DROP is the same as deleting whatever objects you reference in your DROP statement. It is the easiest and quickest syntax and is pretty much the same for all major SQL Server objects (database, tables, views, triggers). Be careful with this syntax, don’t use drop unless you are sure about it. It looks like this, note that putting the word USE Accounting to make sure we are deleting the intended table stored in the respective database:
USE
AccountingDROP TABLE Employees
The result will be TABLE DROPPED. The syntax is much the same for dropping the entire database. Now let us drop the Accounting database:
USE masterDROP DATABASE Accounting
You will see the following output on the screen:
Deleting
database file ‘C
:Program FilesMicrosoft SQL
ServerMSSQLdataCustomerdata.mdf’.Deleting database file ‘C:Program
FilesMicrosoft SQL ServerMSSQLdataCustomerLog.ldf’.
Sometimes you might get an error that says "Database cannot be deleted." If this happens check to see that the database you are using currently in the Query Analyzer is something other the one you are deleting. Also make sure you don’t have any connections open for the database you are trying to drop.
So bottom line, we have learned to CREATE our own database, add some tables to it. We took a ride through the various situations as how to ALTER our existing tables and databases. Last but not least we also saw how to DROP our database objects if need arises. We will see more of ALTER statements when we discuss adding and dropping constraints in our next part.
/ 31 
