October 1, 2002 at 9:06 am
I was reading the article "Beware to the System Generated Constraint Name" by Gregory A. Larsen. I am using DMO the create my tables, columns, and primary keys. Would you have an example of SQL-DMO code that assigns a constraint name instead of letting the system generate one.
I am still getting an error from time to time on certain systems when MSDE is trying to generate a primary key constraint name. I would like to name the constraint like this: PK_tablename, but don't know where to do this in my DMO code. Thanks.
October 1, 2002 at 8:45 pm
Give me a day or two I'll see what I can find. Kinda busy at work these days!
Andy
October 2, 2002 at 5:02 am
Hi,
I found this in the sql-dmo samples
first create the table
then add the primary key constraint like this
Private Sub CreateMyTable()
On Error GoTo ErrorHandler
'This example illustrates table creation. Storage for large text and BLOB data in
'the table is assigned from a non-default filegroup.
Dim oDatabase As SQLDMO.Database
'New table
Dim oMyTable As New SQLDMO.Table
'New table columns
Dim colMyTableID As New SQLDMO.Column
Dim colMyTableName As New SQLDMO.Column
Dim colMyTableDescription As New SQLDMO.Column
Dim colMyTablePicture As New SQLDMO.Column
Dim sDatabase As String
sDatabase = lstDatabases.SelectedItem
If vbNo = MsgBox("Do you want to add MyTable to " & lstDatabases.SelectedItem & "." & lstTables.Text, vbYesNo) Then
Exit Sub
End If
' Get the database.
Set oDatabase = goSQLServer.Databases(sDatabase)
' Populate the Column objects to define the table columns.
colMyTableID.Name = "MyTableID"
colMyTableID.Datatype = "int"
colMyTableID.Identity = True
colMyTableID.IdentityIncrement = 1
colMyTableID.IdentitySeed = 1
colMyTableID.AllowNulls = False
colMyTableName.Name = "MyTableName"
colMyTableName.Datatype = "varchar"
colMyTableName.Length = 15
colMyTableName.AllowNulls = False
colMyTableDescription.Name = "MyTableDescription"
colMyTableDescription.Datatype = "text"
colMyTableDescription.AllowNulls = True
colMyTablePicture.Name = "MyTablePicture"
colMyTablePicture.Datatype = "image"
colMyTablePicture.AllowNulls = True
' Name the table, then set desired properties to control eventual table
' construction.
oMyTable.Name = "MyTable"
oMyTable.FileGroup = "PRIMARY"
' Add populated Column objects to the Columns collection of the
' Table object.
oMyTable.Columns.Add colMyTableID
oMyTable.Columns.Add colMyTableName
oMyTable.Columns.Add colMyTableDescription
oMyTable.Columns.Add colMyTablePicture
' Create the table by adding the Table object to its containing
' collection.
oDatabase.Tables.Add oMyTable
'
'
'In a second step
'Now create a PRIMARY key
Dim keyPKMyTable As New SQLDMO.Key
Dim namesPKMyTable As SQLDMO.Names
' Create the primary, clustered key on mytableid
keyPKMyTable.Clustered = True
keyPKMyTable.Type = SQLDMOKey_Primary
'give constraint a name
keyPKMyTable.Name = "pk_mytable"
' Use the Names collection to define the constraint on the
Set namesPKMyTable = keyPKMyTable.KeyColumns
namesPKMyTable.Add "MyTableID"
' Mark start of change unit.
oMyTable.BeginAlter
' Add the populated Key object to the Keys collection of the
' Table object.
oMyTable.Keys.Add keyPKMyTable
' Create the PRIMARY KEY constraint by committing the unit of change.
oMyTable.DoAlter
Exit Sub
ErrorHandler:
MsgBox Err.Description
End Sub
October 2, 2002 at 8:12 am
Thank you SO much. It works!
It was the line: keyPKMyTable.Name = "pk_mytable" that I was looking for! 🙂
Where did you find the sql-dmo samples?
October 2, 2002 at 8:23 am
Hi , the sql-dmo samples are installed (i used the default installation location)
in:
C:\Program Files\Microsoft SQL Server\80\Tools\DevTools\Samples\sqldmo
best regards,
Klaas-Jan
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply