Creating a Table with a Primary Key
Now, let's look at a code example that creates an Orders table that has a primary key based on a column with an Identity property setting. SQL Server table columns that have Identity properties behave as traditional Access table columns with an AutoNumber data type do. This code example resembles the code in Listing 1 in most other ways, except that this sample sets the AllowNulls property to True for the third column.
Listing 2 contains two procedures that create a table named Orders. The Orders table contains an OrderID column followed by OrderDate and ShippedDate columns. The OrderID column serves as the table's primary key, and the ShippedDate column can contain NULL values. The code at callout A in Listing 2 shows the syntax for creating a primary key based on the table's first column. To make SQL Server automatically set the primary key value for new rows, the procedure assigns a value of True to the Identity property. The seed value is 1000 with an increment of 10. After adding the column with an Identity property to the table's design, callout A in Listing 2 instantiates a Key object, key1, and assigns Name and Type properties for key1. The code sets key1's Clustered property to True so that the primary key's unique index is created as a clustered index for the Orders table. Before adding key1 to the Keys collection for the Orders table, you must designate at least one column on which to designate the primary key. The sample uses the col1 object, which has an Identity property setting.
All the columns in Listing 1 and the first two columns in Listing 2 require field values. However, the values in Listing 2's third column, ShippedDate, are different. A ShippedDate value is NULL before an order ships. Therefore, the code at callout B in Listing 2 assigns the value of True to the third column's AllowNulls property. The default value for this property is False.
Creating a Table with Foreign Keys
The Products table from Listing 1 and the Orders table from Listing 2 are in a many-to-many relationship. This relationship exists because any product can be included in more than one order, and any order can have more than one product. To include this relationship in this article's database design, the database needs two design updates. First, you need to redesign the Products table so that it has a primary key. Second, you need to add a new table to the database that links the Products and Orders tables by storing data common to the domains of both tables. For example, you can store the quantity of each product within a line item on an order in a new OrderDetails table that links the Products and Orders tables.
The sample Access project file for this article includes procedures that automate the process of adding a primary key to the Products table. (For download instructions for this file, see the More on the Web box.) To invoke these procedures, run the CallRemoveOriginalProductIDColumnAndAddNewProductIDColumn procedure from the Visual Basic Editor window for the project. This procedure calls two other procedures. The first called procedure removes the original ProdID column from the Products table. The second called procedure adds a replacement ProdID column; this replacement column has an Identity property that has a Key object defined on the column. The Key object has a Type setting for a primary key.
Web Listing 1 shows two procedures for creating the OrderDetails table. This table has foreign keys that link it to the Orders table and the Products table. Web Listing 1 also shows the syntax that you use to base a primary key on more than one column. The code at callout A starts by adding two columns to the table. The third block of code within the callout creates a foreign key. The code for the foreign key starts by instantiating a Key object. After naming the object, the code sets its foreign key Type property. Next, the code adds the Name property for the col1 object to the key's KeyColumns collection. This specification designates the OrderID column in the OrderDetails table as the local column for the foreign key. Next, the code designates the Orders table and its OrderID column as the referenced table and referenced column. After designating these property settings, the code for the first foreign key concludes by adding the key to the Keys collection of the OrderDetails table. The remainder of callout A creates a foreign key that points to the ProdID column in the Products table from the ProdID column in the OrderDetails table.
The code at callout B in Web Listing 1 shows the syntax for basing a primary key on more than one column. This syntax is similar to the approach that callout A takes, except that this time you invoke the Add method for the key's KeyColumns collection once for each column on which you define the primary key. For example, the code at callout B invokes the method once for the Name property value of the col1 object and a second time for the Name property value of the col2 object. Another distinction between this code sample for setting the primary key and the sample in callout A in Listing 2 is that this code specifies a nonclustered index.
SQL-DMO offers a powerful programming solution for automating the administrative tasks that you perform manually with SQL Server. You can use the three code samples in this article to develop your programming techniques for building tables. Practicing these techniques will help you think of new ways to use SQL-DMO to automate your processes.