public AddForeignKeyResult AddForeignKey(string referencingTableName, string referencingColumnName, string referencedTableName, string referencedKeyColumn, DatabaseAgent agent)
{
string foreignKeyName = StringExtensions.RandomString(31, false, false);
DaoForeignKeyColumn foreignKeyColumn = new DaoForeignKeyColumn(referencingColumnName, -1, foreignKeyName, referencedKeyColumn, referencedTableName);
DaoForeignKeyInfo foreignKey = new DaoForeignKeyInfo(foreignKeyColumn, referencingTableName);
return(this.AddForeignKey(foreignKey, agent));
}
private AddForeignKeyResult AddForeignKey(DaoForeignKeyInfo foreignKey, DatabaseAgent agent)
{
if (agent.DbType == DaoDbType.SQLite)
{
return(AddForeignKeyResult.Success); // foreign key constraints aren't supported in SQLite
}
string foreignKeyName = string.Empty;
try
{
foreignKeyName = foreignKey.DaoForeignKeyColumn.ForeignKeyName;
if (agent.DbType == DaoDbType.Firebird)
{
foreignKeyName = StringExtensions.RandomString(31, false, false);
}
// table1Name, fkName, column1Name, table2Name, column2Name
agent.ExecuteSql(string.Format(
DatabaseAgent.ADDFOREIGNKEYFORMAT,
foreignKey.TableName,
foreignKeyName,
foreignKey.DaoForeignKeyColumn.ColumnName,
foreignKey.DaoForeignKeyColumn.ReferencedTable,
foreignKey.DaoForeignKeyColumn.ReferencedKey));
}
catch (FbException ex)
{
if (ex.ErrorCode == -2147467259)// foreign key already exists
{
return(AddForeignKeyResult.Success);
}
}
catch (SqlException sqlEx)
{
if (sqlEx.Message.ToLower().Contains(string.Format("already an object named '{0}'", foreignKeyName).ToLower()))
{
return(AddForeignKeyResult.Success);
}
else
{
return(AddForeignKeyResult.Error);
}
}
catch (Exception ex)
{
Console.WriteLine(string.Format("An error occurred adding foreign key: {0}\r\n{1}", ex.Message, ex.StackTrace));
return(AddForeignKeyResult.Error);
}
return(AddForeignKeyResult.Success);
}