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));
}
}