Change, as they say, is constant. While it would be nice if once we created our objects we didn’t have to alter them again, the reality is just as our data changes, the objects that support that data will often change as well. While making a change to a view is straightforward, changing a table can be a bit more complicated.

Changing a Table

Two common changes we make to tables are modifying the data type of a column or adding a column to a table. When we modify a column, we can only make a change that will support the data currently in the table. For example, if we had a text column that was currently 60 characters in size and the column included some text that was 50 characters long, we would be unable to make the size of the column 40 characters because we already have data larger than that. We could grow the column size to 100 if we needed to hold larger data because that will not interfere with the data already in the table.

There are two parts to altering a table. First we use the keyword ALTER TABLE and provide the table name. Then we use the keyword ALTER COLUMN and provide the table name to specify the change. If we needed to store longer names in our PetOwner table, we could expand the OwnerName column from 60 to 100.

ALTER TABLE PetOwner ALTER COLUMN OwnerName VARCHAR (100);

OUTPUT

Command(s) completed successfully.

Adding a Column

We can add a column to the table by using the keywords ALTER TABLE, followed by the name of the table, and then use the keyword ADD and specify the column name and the data type. If we do not specify the column is required, the default is NULL, meaning optional. Adding a column for a nickname to our Dog table would use the statement below.

ALTER TABLE Dog ADD PetNickName VARCHAR(50);

OUTPUT

Command(s) completed successfully.

In most RDBMS, the column will be added to the end of the table. Because of the difficulty in switching the order of the columns, the columns are left in their default order and new columns are added at the end.

Note—some changes to a table are not allowed while data is present in the table. For example, if we have a column of data type VARCHAR (100) and there is a row with 100 characters in that column, we would be unable to change the data type to VARCHAR(60). Other changes actually require us to create a new table, migrate the data over and rename the table.

Adding a Foreign Key

After a two tables we want to relate are created, we can create the link or relationship between these tables. We normally link a primary key in one table to a column in the second table. In chapter 13, we created a new table called Fish and we want to link this the PetOwner table. Let’s link these two tables. The foreign key will exist on the Fish table because the primary key, OwnerIdentifier, belongs to the PetOwner Table.

Adding a foreign key uses several keywords beginning with ALTER TABLE. We then specify the name of the table where the foreign key will be created. This is sometimes called the child table. We use the keywords ADD CONSTRAINT with the name of the foreign key and the column it will use. Lastly we use the key word REFERENCES to link to the PRIMARY KEY column of the other or ‘parent’ table. Our foreign key is now created.

ALTER TABLE Fish ADD CONSTRAINT RefPetOwner6 FOREIGN KEY (OwnerIdentifier) REFERENCES PetOwner(OwnerIdentifier);

Removing Tables

Once a table is no longer needed, the keyword DROP TABLE with the name of the table will remove the table from the system. Be careful—dropping a table in a production environment is normally a BIG deal.

DROP TABLE [TableName];

Views

Making a change to a view uses the keyword ALTER; however, a view does not have individual pieces to change—it is all or nothing. We change or alter a view using the same syntax as the CREATE VIEW statement, changing the word CREATE to ALTER. Let’s change the view v_SearchForGarfield view to use a LIKE instead of an equal operator.

ALTER VIEW v_SearchForGarfield
AS
(
Select *
FROM Cat
WHERE Cat.NameOfCat LIKE 'Gar%'
);

OUTPUT

Command(s) completed successfully.