Key Object (SQL-DMO)

The Key object exposes the attributes of Microsoft® SQL Server™ table keys.

Remarks

SQL Server tables can contain key constraints. The constraints apply declarative referential integrity to the data contained in the table. Keys can be primary or foreign. A single primary key can be defined on a table, though many foreign keys can be defined, constraining data for a column or columns to values existing as primary key values in other tables.

With the Key object, you can:

• Define a PRIMARY KEY constraint for a SQL Server table.
• Remove a PRIMARY KEY constraint from a SQL Server table.
• Define a FOREIGN KEY constraint for a SQL Server table.
• Remove a FOREIGN KEY constraint from a SQL Server table.
• Rebuild the index used to maintain a PRIMARY KEY constraint on a table.

The Name property of a Key object uses the SQL Server data type sysname. The value of the Name property must be unique within a SQL Server database. The Name property is not required when using a Key object to define a new SQL Server PRIMARY or FOREIGN KEY constraint. When not specified, SQL-DMO generates a Name property.

To define a PRIMARY KEY constraint on a SQL Server table

1. Create a Key object.
2. Set the Type property to SQLDMOKey_Primary.
3. Set the Clustered property to True to create a SQL Server clustered index if clustering is a desired attribute of the PRIMARY KEY constraint.
4. Get the KeyColumns Names collection from the Key object.
5. Add the PRIMARY KEY column names to the Names collection. The order in which column names are added determines the order of column participation in the index maintaining the PRIMARY KEY constraint.
6. Add the Key object to the Keys collection of a Table object that exposes the attributes of the SQL Server table.

To define a FOREIGN KEY constraint on a SQL Server table

1. Create a Key object.
2. Set the Type property to SQLDMOKey_Foreign.
3. Get the KeyColumns Names collection from the Key object.
4. Add the FOREIGN KEY column names to the Names collection. The Names collection contains the names of the column or columns that make up the FOREIGN KEY constraint.
5. Set the ReferencedTable property to the name of the SQL Server table containing the PRIMARY KEY constraint to be referenced by the FOREIGN KEY constraint.
6. Get the ReferencedColumns Names collection from the Key object.
7. Add the name of the columns participating in the PRIMARY KEY constraint of the specified table to the ReferencedColumns Names collection.
8. Add the Key object to the Keys collection of a Table object that exposes the attributes of the SQL Server table to receive the FOREIGN KEY constraint.

Methods

Properties