ALTER TABLE changes the structure or definition of an existing table. For example, adding CategoryID to title changes the table without adding rows; the new column initially contains NULL values. section 18.2.1
Use ALTER TABLE for several kinds of table changes:
- Add a column by naming the table, column, and data type: ALTER TABLE title ADD CategoryID int; section 18.2.1
- Add a named primary-key constraint, such as PK_category, to enforce uniqueness and identify the key clearly. section 18.3.2
- Add a foreign-key constraint to connect columns in related tables, such as title.CategoryID and category.CategoryID. section 18.4.2
- Remove a constraint or column with DROP, such as DROP CONSTRAINT, DROP PRIMARY KEY, DROP FOREIGN KEY, or DROP COLUMN. section 18.5.1 section 18.5.2
Add and connect a category column
ALTER TABLE title ADD CategoryID int; After the column exists, UPDATE fills CategoryID for existing title rows. A foreign key can then protect the relationship: ALTER TABLE title ADD CONSTRAINT FK_title_category FOREIGN KEY (CategoryID) REFERENCES category(CategoryID); Check existing data before adding constraints: values that violate the new rules cause an error. Creating constraints before inserting rows is usually safer. section 18.2.1 section 18.4.2
ALTER TABLE title ADD CategoryID int;
View the complete column-addition statement used for the title table. section 18.2.1
