System generated constraint names

Question

Post reply

System generated constraint names

anitae

SSC Veteran

Points: 295
More actions
October 1, 2002 at 9:06 am

#79471

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.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Andy Warren SSC Guru Points: 119747 More actions · Answer 1

Andy Warren

SSC Guru

Points: 119747

October 1, 2002 at 8:45 pm

#437602

Give me a day or two I'll see what I can find. Kinda busy at work these days!

Andy

http://qa.sqlservercentral.com/columnists/awarren/

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter

Klaas-Jan SSCrazy Points: 2297 More actions · Answer 2

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