public void CreateMultiColumnUniqueConstraint() { const string TableName = nameof(CreateMultiColumnUniqueConstraint); Table table = new Table(TableName); Column column1 = new Column("Unique1", DataType.Int()); Column column2 = new Column("Unique2", DataType.Int()); Column column3 = new Column("Unique3", DataType.Int()); table.Columns.AddAll(column1, column2, column3); UniqueConstraint uniqueConstraint = new UniqueConstraint("UniqueConstraint"); uniqueConstraint.AddColumns(column1, column2, column3); table.Constraints.Add(uniqueConstraint); VerifyUniqueConstraint(TableName, table); }
public void CreateTableWithMultipleConstraints() { const string PrimaryKeyColumnName = "PK"; const string UniqueColumnName1 = "UQ1"; const string UniqueColumnName2 = "UQ2"; string tableName = nameof(CreateTableWithMultipleConstraints); Table table = new Table(tableName); Column column1 = new Column(PrimaryKeyColumnName, DataType.Int()); Column column2 = new Column(UniqueColumnName1, DataType.Char(10)); Column column3 = new Column(UniqueColumnName2, DataType.Money()); table.Columns.AddAll(column1, column2, column3); PrimaryKeyConstraint primaryKeyConstraint = new PrimaryKeyConstraint { IndexType = IndexType.NONCLUSTERED }; primaryKeyConstraint.AddColumn(column1); UniqueConstraint uniqueConstraint = new UniqueConstraint { IndexType = IndexType.CLUSTERED }; uniqueConstraint.AddColumns(Tuple.Create(column2, ColumnSort.DESC), Tuple.Create(column3, ColumnSort.ASC)); table.Constraints.AddAll(primaryKeyConstraint, uniqueConstraint); using (SqlConnection sqlConnection = new SqlConnection(connectionString)) { sqlConnection.Open(); Assert.False(table.IsTablePresentInDatabase(sqlConnection)); table.Create(sqlConnection); Assert.True(table.IsTablePresentInDatabase(sqlConnection)); using (SqlCommand sqlCommand = sqlConnection.CreateCommand()) { string sql = $@" SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey') = 1 AND TABLE_NAME = '{tableName}'"; sqlCommand.CommandText = sql; string primaryKeycolumnNameResult = (string)sqlCommand.ExecuteScalar(); Assert.Equal(PrimaryKeyColumnName, primaryKeycolumnNameResult); sql = $@" SELECT COUNT(COLUMN_NAME) FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsUniqueCnst') = 1 AND TABLE_NAME = '{tableName}'"; sqlCommand.CommandText = sql; int columnCount = (int)sqlCommand.ExecuteScalar(); Assert.Equal(2, columnCount); } table.Drop(sqlConnection); Assert.False(table.IsTablePresentInDatabase(sqlConnection)); } }