Adding a Primary Key column to an existing populated table

Primary Key is a basic requirement for establishing relationship between tables. Two or more tables are related by means of Primary Key and Foreign Key relationships.

Can you add a Primary Key column to a existing populated table?

The answer is, yes you can, you can use T-SQL code or SQL Server Management Studio to add a column. Modification of objects is at the heart of maintenance.

This post shows you how to add a Primary Key column, step-by-step. I will be using SQL Server 2012 but the procedure is similar with probably some very minor variations in other versions.

The first thing is to make sure that when you save changes the table is not recreated. You can do this by removing the check mark from here (Prevent saving changes that require table re-creation).
The Options menu is accessed from Tools | Options... .

Creating a table with three columns:

I will be creating a new table in a database Manoa in an instance of SQL Server. Using the drop-down shown you can create a new table.

 

When you click New Table... above the next pane opens in SSMS with a default table name Table_1 which can be changed to the one you give after configuring it as shown.  Enter Fname under ColumnName, then Data Type comes up as nchar(10). If it does not you can select from the list. Place check mark for Allow Nulls (which means this column may have a value or may not, it is nothing unless something is entered).

 

When you close this window, the next window is displayed.

 

When you click Yes, the above table will be saved and you get the opportunity of overwriting the default name with one of your own as shown.

'

Herein table name was changed to Client. The table Client gets created which you can see in the Table node in Object Explorer. You may need to refresh the Table node (by right clicking and choosing Refresh). The next picture shows the columns of the Client table and the columns that you added.

Populating the table with 3 rows

Right click dbo.Client under the Tables node. From the drop-down select 'Edit Top 200 Rows '.  The next window will be displayed with just the first two with all Nulls. Enter the names by clicking on the Nulls under Lname; then under Fname and then under age. When you finish the 1st row, the second row is displayed again with Nulls. Repeat the above using names shown (you may user your own but of proper data type) for rows 2 and 3. We assume only 3 rows to exist in this table.  It can contain any number of rows. Click the Close(x) button.

 

The table gets saved with the values you entered. When you run the Select query as shown, you will see the three rows with values you entered. The column with no name shows just the row numbers.

Adding the Identity column

Right click dbo.Client table in Object Explorer in the Tables node. The following window with two panes open as shown. You may need to click the 4th row below the one with Lname 'Amanda' in the above to add the Id column.

For ColumnName enter 'Id'. For DataType enter or choose 'tinyint' and place check mark for Allow Nulls as shown. The bottom pane is automatically populated as shown.

In the bottom pane scroll down to the line shown, 'Identity Specification' .  Change from 'No' to 'Yes' indicating you want Id to be an identity column. The Identity Increment and Identity Seed gets filled with default values and you will notice that the check mark for Allow Nulls is removed as soon as you make these changes.

 

 

After making Id the identity column your table design is displayed as shown.

Again run a Select statement as shown and you will see the Id column as being added to your table.

 

Adding the Primary Key
 Now that you have added the Identity column, you will now designate that column to contain the Primary Key.

Open the table in design view.  Highlight the row containing Id and then right click that row to open a drop-down menu as shown.

 

 

Choose, Set Primary Key. The Id column will now show a 'Key' left to the column as shown.

 

After the above step, you will have set the Identity column to have the Primary Key. Expand the dbo.Client table as shown to see the 4 columns and the Key you added.

This completes the procedure to add an identity column to a pre-existing, populated table.

 

 

Creating a table using Power Shell and SQL Server Management Objects

Review this previous post    http://hodentekmsss.blogspot.com/2014/11/accessing--
sql-server-via-smo-using.html

In the above post you will get an introduction to Power Shell and SQL Server
Management Objects. You also learn how to access the SQL Server and carry out a
few tasks like listing out all the databases.

Review this post for creating a database using Power Shell and SQL Server
Management Objects:
http://hodentekmsss.blogspot.com/2015/02/creating-database-using-power-shell-
and.html

In the above post you will learn how to create a new database in SQL Server using
SMO and Power Shell. Using Transact-SQL or the SSMS you can easily create a
database. Using PowerShell is another option to create a database using a script.

Having created a database you would want to create tables in the database.

This post shows how you may create a table. This script has been tested to work with
Power Shell ver. 4.0.

Launch SQLPS from Start | Run and type-in SQLPS and click OK.

PS_SMOTable01.png

Let us get connected to the instance of SQL Server (RegencyPark) on this computer
(Hodentek8) by (declaring) running the command shown.

PS SQLSERVER:\>  $server= new-object Microsoft.Sqlserver.management.smo.server
'Hodentek8\RegencyPark'

Let us create a database named 'Feb6'.
Declare a name for the database as shown here:
PS SQLSERVER:\SQL\HODENTEK8\REGENCYPARK> $dbname= "Feb6"

Now create a database object variable in the instance using SMO:
PS SQLSERVER:\> $db=new-object  Microsoft.Sqlserver.management.smo.Database
($server,$dbname)

Now create the database using the create () function.
PS SQLSERVER:\>  $db.Create()
When you run the above command you will have created a database called Feb6 in the
SQL Server instance as shown (this may take a few seconds and you will not get any response except if there are errors) in SQL Server Management Studio:

PS_SMOTable02.png

In creating the table run the following lines of command in PS SQLSERVER:\>:

We declare a name for the table as 'SmoCustomers' and instantiate the table object
by running the following:
$tbname = "SmoCustomers"
$tb = new-object  Microsoft.Sqlserver.management.smo.Table ($db, $tbname)

We assume the table to have three columns (fields) named 'Field1, Field2 and
Field3. Field1 is of int data type and the two others are nvarchar(num) data type.

Typically instantiate a column object and its data type and add that column to the
$tb.Columns. Carry out this for the three columns by running these lines in PS
SQLSERVER:\>.

$colField1 = New-Object Microsoft.SqlServer.Management.Smo.Column ($tb, "Field1")
 $colField1.DataType = [Microsoft.SqlServer.Management.Smo.Datatype]::Int
 $tb.Columns.Add($colField1)

$colField2 = New-Object Microsoft.SqlServer.Management.Smo.Column ($tb, "Field2")
 $colField2.DataType = [Microsoft.SqlServer.Management.Smo.Datatype]::NVarchar(25)
 $tb.Columns.Add($colField2)

$colField3 = New-Object Microsoft.SqlServer.Management.Smo.Column ($tb, "Field3")
$colField3.DataType = [Microsoft.SqlServer.Management.Smo.Datatype]::NVarchar(25)
$tb.Columns.Add($colField3)
Now create the table by running the following code:
$tb.create ()

 

Here is the complete line-by-line code:

PS_SMOTable03.png
Again do not look out for a response but check for the table in Feb6 database you
created earlier.

PS_SMOTable04.png