How to create CHECK CONSTRAINT with SMO

How to create CHECK CONSTRAINT with SMO

• albidochon

  There have been a number of cases where I've used Transact-SQL to solve a problem where I couldn't find the solution within SMO, but in this case it might be worth your while to check out the AddDefaultConstraint method of the Column object, as I mentioned in my first post. The argument you'd use with the method would be the myForeignKey.Name property.
  
  

• Chris Honcoop

  On second thought, you probably want to work with the ForeignKey object. Here's an example from BooksOnline that should help:

  'Connect to the local, default instance of SQL Server.
  Dim srv As Server
  srv = New Server
  'Reference the AdventureWorks database.
  Dim db As Database
  db = srv.Databases("AdventureWorks")
  'Declare a Table object variable and reference the Employee table.
  Dim tbe As Table
  tbe = db.Tables("Employee", "HumanResources")
  'Declare another Table object variable and reference the EmployeeAddress table.
  Dim tbea As Table
  tbea = db.Tables("EmployeeAddress", "HumanResources")
  'Define a Foreign Key object variable by supplying the EmployeeAddress as the parent table and the foreign key name in the constructor.
  Dim fk As ForeignKey
  fk = New ForeignKey(tbea, "test_foreignkey")
  'Add EmployeeID as the foreign key column.
  Dim fkc As ForeignKeyColumn
  fkc = New ForeignKeyColumn(fk, "EmployeeID", "EmployeeID")
  fk.Columns.Add(fkc)
  'Set the referenced table and schema.
  fk.ReferencedTable = "Employee"
  fk.ReferencedTableSchema = "HumanResources"
  'Create the foreign key on the instance of SQL Server.
  fk.Create()

  
  
  

• MMV2007

  OK, got it. Much simpler than it should have been given the amount of time spent.

  string sqlCmd = "ALTER TABLE [" + myTable.Name + "] CHECK CONSTRAINT [" + myForeignKey.Name + "]";

  myDB.ExecuteNonQuery(sqlCmd);

  
  
  

• AWOLMAN

  That creates the foreign key, but not the Check Constraint.
  
  

• NMM

  Have you looked at the AddDefaultConstraint method of the Column object
  
  
• How to create CHECK CONSTRAINT with SMO