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(); primaryKeyConstraint.IndexType = IndexType.NONCLUSTERED; primaryKeyConstraint.AddColumn(column1); UniqueConstraint uniqueConstraint = new UniqueConstraint(); 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)); } }
public void CreateTableWithSingleColumnPrimaryKey() { const string ColumnName = "Id"; const string TableName = nameof(CreateTableWithSingleColumnPrimaryKey); Table table = new Table(TableName); Column column = new Column(ColumnName, DataType.Int()); table.Columns.Add(column); PrimaryKeyConstraint primaryKeyConstraint = new PrimaryKeyConstraint("PrimaryKeyConstraint"); primaryKeyConstraint.AddColumn(column); table.Constraints.Add(primaryKeyConstraint); VerifyPrimaryKey(ColumnName, TableName, table); }