Altering a Table by Adding a PRIMARY KEY Constraint

SQL-DMO will be removed in the next version of Microsoft SQL Server. Avoid using SQL-DMO feature in new development work, and plan to modify applications that use SQL-DMO.

This example illustrates primary key definition using the SQL Distributed Management Objects (SQL-DMO) Key object. In the example, adding the Key object to the Keys collection creates a clustered, PRIMARY KEY constraint on the referenced table.

Dim tableCategories As SQLDMO.Table

Dim keyPKCategories As New SQLDMO.Key
Dim namesPKCategories As SQLDMO.Names

' Get the Categories table. Note: Create and connect of SQLServer
' object used is not illustrated in this example.
Set tableCategories = _ 
    oSQLServer.Databases("Northwind").Tables("Categories")

' Create the primary, clustered key on CategoryID.
keyPKCategories.Clustered = True
keyPKCategories.Type = SQLDMOKey_Primary

' Use the Names collection to define the constraint on the
' CategoryID column.
Set namesPKCategories = _
    keyPKCategories.KeyColumnsnamesPKCategories.Add "CategoryID"

' Mark start of change unit.
tableCategories.BeginAlter

' Add the populated Key object to the Keys collection of the
' Table object.
tableCategories.Keys.Add keyPKCategories

' Create the PRIMARY KEY constraint by committing the unit of change.
tableCategories.DoAlter