public void CreateTableWithMultiColumnPrimaryKey()
{
const string ColumnName1 = "Id";
const string ColumnName2 = "Id2";
const string ColumnName3 = "Id3";
const string TableName = nameof(CreateTableWithMultiColumnPrimaryKey);
Table table = new Table(TableName);
Column column = new Column(ColumnName1, DataType.Int());
Column column2 = new Column(ColumnName2, DataType.Int());
Column column3 = new Column(ColumnName3, DataType.Int());
table.Columns.AddAll(column, column2, column3);
PrimaryKeyConstraint primaryKeyConstraint = new PrimaryKeyConstraint();
primaryKeyConstraint.AddColumns
(
Tuple.Create(column, ColumnSort.ASC),
Tuple.Create(column2, ColumnSort.DESC),
Tuple.Create(column3, ColumnSort.ASC)
);
table.Constraints.Add(primaryKeyConstraint);
VerifyPrimaryKey(ColumnName1, TableName, table);
}
public void CreateMultiColumnForeignKeyConstraints()
{
const string ForeignKeyColumnName1 = "PersonId";
const string ForeignKeyColumnName2 = "PersonName";
Table primaryTable = new Table(nameof(CreateMultiColumnForeignKeyConstraints) + "Persons");
Column id = new Column("Id", DataType.Int())
{
Nullable = false
};
Column lastName = new Column("LastName", DataType.VarChar(255))
{
Nullable = false
};
Column firstName = new Column("FirstName", DataType.VarChar(255));
Column age = new Column("Age", DataType.Int());
primaryTable.Columns.AddAll(id, lastName, firstName, age);
PrimaryKeyConstraint primaryTablePrimaryKeyConstraint = new PrimaryKeyConstraint();
primaryTablePrimaryKeyConstraint.AddColumns(id, lastName);
primaryTable.Constraints.Add(primaryTablePrimaryKeyConstraint);
Table foreignTable = new Table(nameof(CreateMultiColumnForeignKeyConstraints) + "Orders");
Column orderId = new Column("OrderId", DataType.Int())
{
Nullable = false
};
Column orderNumber = new Column("OrderNumber", DataType.Int())
{
Nullable = false
};
Column personId = new Column(ForeignKeyColumnName1, DataType.Int());
Column personLastName = new Column(ForeignKeyColumnName2, DataType.VarChar(255));
foreignTable.Columns.AddAll(orderId, orderNumber, personId, personLastName);
PrimaryKeyConstraint foreignTableprimaryKeyConstraint = new PrimaryKeyConstraint(orderId);
ForeignKeyConstraint foreignKeyConstraint = new ForeignKeyConstraint("ForeignKeyConstraint");
foreignKeyConstraint.AddColumns(personId, personLastName)
.References(primaryTable)
.AddReferenceColumns(id, lastName);
foreignTable.Constraints.AddAll(foreignTableprimaryKeyConstraint, foreignKeyConstraint);
VerifyForeignKey(primaryTable, foreignTable);
}
public void CreateTableWithMultiColumnPrimaryKeyWithColumnSort()
{
const string ColumnName1 = "Id";
const string ColumnName2 = "Id2";
const string TableName = nameof(CreateTableWithMultiColumnPrimaryKeyWithColumnSort);
Table table = new Table(TableName);
Column column = new Column(ColumnName1, DataType.Int());
Column column2 = new Column(ColumnName2, DataType.Int());
table.Columns.AddAll(column, column2);
PrimaryKeyConstraint primaryKeyConstraint = new PrimaryKeyConstraint("PrimaryKeyConstraint", column, ColumnSort.DESC);
primaryKeyConstraint.AddColumns(column2);
table.Constraints.Add(primaryKeyConstraint);
VerifyPrimaryKey(TableName, table);
}