Modifying existing SQL tables is a common use case as the data models for your application change over time. The most basic modification you are likely to do is adding a new column to a SQL table to capture more data.

Simple changes can be done using SQL Server management software that provides a convenient user interface to interact with your database and tables. For more complex operations, using scripted commands in the Transact-SQL [(T-SQL)](https://docs.microsoft.com/en-us/sql/t-sql/language-reference?view=sql-server-ver16) language is usually preferable. Using T-SQL provides the greatest flexibility to fine-tune the table adjustments, and it makes it very easy to keep a record of the changes made to the table.

To add a column to an existing SQL Server table we are going to use the **ALTER** keyword. **ALTER** does more than just allowing us to add columns to a table, it is also used to delete or modify existing columns. It can further be used to add, modify or drop foreign keys on the table columns, as well as modifying constraints like *UNIQUE* or *NOT NULL* that can be placed on columns to protect data integrity.

The quickest way to add a new column is without any additional configuration:

```sql
ALTER TABLE table_name
ADD column_name datatype;
```

Where the *datatype* is replaced with any valid data type as listed [here by Microsoft](https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-types-transact-sql?view=sql-server-ver16).

We can append additional configuration to this basic command to define specific requirements for the new column.

Here we are placing a *UNIQUE* constraint to enforce unique values in the new column:

```sql
ALTER TABLE table_name
ADD column_name datatype;
ALTER TABLE table_name 
ADD UNIQUE (column_name);
```

Adding a column that may not contain NULL values is a bit more complicated. We have to satisfy the *NOT NULL* constraint right away, meaning we can't create a column that can't be null without providing values for it. The solution is to provide a DEFAULT value:

```sql
ALTER TABLE table_name
ADD column_name datatype
NOT NULL
DEFAULT default_value
```

The chosen *default_value* depends on the datatype of the column being added and the requirements of your application, e.g. '0' for a column of strings.

Finally, let's add a column of auto-incrementing integers. For this example we use the **IDENTITY** keyword, which requires a seed value for the first row and an an increment to number the following rows. Both the seed and the increment are set to 1 here, the result is a new column containing values starting 1 to the number of rows in the table.

```sql
ALTER TABLE table_name
ADD column_name INT IDENTITY(1,1)
```

The **IDENTITY** keyword does the magic to make the column auto-increment, but be aware that there can only be one identity column per table. Due to its convenient set up and inherent uniqueness, the identity column is commonly used as the *PRIMARY KEY* column of the table, although this is not mandatory.

Two more constraints you may want to apply while adding a new SQL Server column are *FOREIGN KEY* and *CHECK*. *FOREIGN KEY* is used when a column needs to connect a relationship to another column in a different table, for example to only allow orders against a known product id for an online shop. The *CHECK* constraint is used to evaluate column entries against a predefined rule - e.g. value must be larger than 0 for a quantity or price column.

Modifying existing SQL tables is a common use case as the data models for your application change over time. The most basic of these modifications is adding a new column to a SQL table to capture more data.

How to Add a Column to a Microsoft SQL Server Table

Configuring the auto-increment property to a column is a simple SQL command. The complexities come in when trying to deal with either a) tables that have already been created, b) tables that already have data rows in them, and c) when you need to add auto-increment to an existing Primary Key column.

The complexities arise from the fact that adding the auto-increment requires a full table rebuild. If the table has already been created and/or has data rows then adding the auto-increment is more complex.

## Adding to a New Table 

```sql
CREATE TABLE Users (
  Id INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  Email VARCHAR(100) NOT NULL,
  Firstname VARCHAR(100),
  Surname VARCHAR(100),
);
```

In this example, we are creating a new table called Users. 

In this table, there are four columns. The Id column is the important one. The keyword IDENTITY is what sets the column to auto-increment by one for every new row added to the table. Additionally, this SQL command sets the Id column as the Primary Key. This means that the column must also be a completely unique index on the table.

The IDENTITY constraint accepts two optional values (both defined as 1 in the example above). The first is the seed value which is essentially the starting number. This can be any valid INT to start from if 1 is not the desired starting point. The second value is the amount to increment each new row by. Setting it to 1 means the number will increase numerically as though it were counting. Setting it to 2 would skip a number each time -> 1,3,5,7,9 etc. Both these values default to 1 and so do not have to be included if you do not need to change them.

## Adding to an Existing Table

There are two options when adding an auto-increment column to an existing table. The first is to create a new table with the IDENTITY column defined, copy all the data rows from the existing table into it, delete the existing table and rename the new table the same as the original:

```sql
-- Create a new temp table with the identity column defined
CREATE TABLE dbo.Tmp_Names
    (
      Id int NOT NULL IDENTITY(1, 1) PRIMARY KEY,
      Name varchar(50) NULL
    )
ON  [PRIMARY]
go

SET IDENTITY_INSERT dbo.Tmp_Names ON
go

-- Update the temp table with the rows from the existing table
IF EXISTS ( SELECT  *
            FROM    dbo.Names ) 
    INSERT  INTO dbo.Tmp_Names ( Id, Name )
            SELECT  Id,Name
            FROM    dbo.Names TABLOCKX
go

SET IDENTITY_INSERT dbo.Tmp_Names OFF
go
-- Delete the existing table
DROP TABLE dbo.Names
go
-- Rename the temp table to match the original table to maintain schema integrity
Exec sp_rename 'Tmp_Names', 'Names'
```

The second option is to add the auto-increment column as a new column to the table, copy the column values across, delete the original column and then rename the new column:

```sql
-- Add a new column to the table
Alter Table Users
Add Id_new Int Identity(1, 1)
Go
-- Set the new column to the original
UPDATE Users
SET Id_new = Id
-- Delete the original column
Alter Table Users Drop Column Id
Go
-- Rename the new column to the original
Exec sp_rename 'Users.Id_new', 'Id', 'Column'
```

One thing to be aware of in this method is that an extra command is required to drop the existing column if it has been defined as the primary key. The name of the PK constraint will depend on what it was called when it was originally created:

```sql
-- Return the name of primary key.  
SELECT name  
FROM sys.key_constraints  
WHERE type = 'PK' AND OBJECT_NAME(parent_object_id) = N'Users';  
GO 
-- Delete the primary key constraint.  
ALTER TABLE Users  
DROP CONSTRAINT PK_Users_Id;   
GO  
```

The first command will return the name of the constraint which must go in place of 'PK_Users_Id' in the second command above.

Knowing how to add auto-incrementing to a numeric column in SQL Server is a skill all database admins should have. It is an exceptionally common use case, whether it be on the Primary Key column of your table, or you need a column to identify insert order or similar.

Add Auto-Increment to a Column in SQL Server

## W﻿hat is a Foreign Key?

I﻿n a SQL database, a foreign key is a column, or combination of columns, in a single table that references another column in a different table. To 'reference' another column means to have the same value as the column it references.

T﻿his reference creates a relationship between the two tables, which is known as a parent-child relationship. In this relationship the parent table is the table that contains the reference column. The child table is the table that contains the column or columns that reference the parent value.

## Why Use a Foreign Key?

T﻿he main purpose of using foreign keys is to maintain referential integrity in the database. The value entered into the foreign key column of the child table **must** exist in the parent table. If the row in the parent table that contains this reference is removed, then the foreign key cannot be entered into the child table.

T﻿his leads to a secondary use of foreign keys. They help enforce business rules in the database. A value cannot be deleted from a parent table if a child table reference is without either cascading queries or more complex business logic.

F﻿oreign keys also make querying easier. The foreign key is traditionally also the column that tables are joined by when querying. Having a clear database structure and documentation about the foreign keys makes it much simpler to query the data.

Foreign Keys are a necessity in any relational database. They help connect distinct data tables together by creating a tightly-bound reference between the tables.