public SQLStatementWriter WriteCreateFKStatement(JMXForeignKey fk, JMXSchema fromSchema = null) { if (fromSchema == null) { fromSchema = _schema; } //Можно сделать сначала с NOCHECK затем CHECK // ALTER TABLE[SysCat].[SysSchemas] WITH CHECK ADD CONSTRAINT[FK_SYSSCHEMAS_SYSAREAS]([SysAreaID]) REFERENCES[SysCat].[SysAreas] ([ID]) // ALTER TABLE[SysCat].[SysSchemas] CHECK CONSTRAINT[FK_SYSSCHEMAS_SYSAREAS] if (fk.CheckOption) { // Enable for new added rows Write($"alter table {fromSchema.DbObjectName} with check add constraint [{fk.KeyName}] foreign key ("); } else { Write($"alter table {fromSchema.DbObjectName} with nocheck add constraint [{fk.KeyName}] foreign key ("); } Write(string.Join(", ", fk.KeyMembers.Select(m => "[" + m.FieldName + "]").ToArray())); Write(")"); Write($"references {fk.RefDbObjectName} ("); Write(string.Join(", ", fk.RefKeyMembers.Select(m => "[" + m.FieldName + "]").ToArray())); Write(")\n"); if (fk.CheckOption) { // check existing rows Write($"alter table {fromSchema.DbObjectName} check constraint [{fk.KeyName}]\n"); } return(this); }
public SQLStatementWriter WriteDropParentRelationStatement(JMXForeignKey fk) { Write("alter table [{0}].[{1}] drop constraint [{2}]\n".ToFormat( fk.RefDbObjectName.AreaName, fk.RefDbObjectName.ObjectName, fk.KeyName)); return(this); }
public SQLStatementWriter WriteDropFKStatement(JMXForeignKey fk, JMXSchema fromSchema = null) { if (fromSchema == null) { fromSchema = _schema; } Write("alter table [{0}].[{1}] drop constraint [{2}]\n".ToFormat( fromSchema.DbObjectName.AreaName, fromSchema.DbObjectName.ObjectName, fk.KeyName)); return(this); }
/// <summary> /// Remove comment && update /// </summary> /// <param name="fk"></param> /// <returns></returns> public SQLStatementWriter WriteCreateParentRelationStatement(JMXForeignKey fk) { //bool withCheck = (bool)fk["CheckOption"]; //string check = withCheck ? "check" : "nocheck"; //// Enable for new added rows //Write("alter table [{0}].[{1}] with {2} add constraint [{3}] foreign key (".ToFormat( // (string)fk["ParentObject"]["AreaName"], // (string)fk["ParentObject"]["ObjectName"], // check, (string)fk["KeyName"])); //Write(string.Join(", ", (fk["KeyMembers"] as JsonArray).Select(m => "[" + (string)m["FieldName"] + "]").ToArray())); //Write(")\n"); //Write($"references [{fk["RefObject"]["AreaName"]}].[{fk["RefObject"]["ObjectName"]}] ("); //Write(string.Join(", ", (fk["RefKeyMembers"] as JsonArray).Select(m => "[" + (string)m["FieldName"] + "]").ToArray())); //Write(")\n"); //if (withCheck) // // check existing rows // Write($"alter table [{fk["ParentObject"]["AreaName"]}].[{fk["ParentObject"]["ObjectName"]}] " + // $"check constraint [{(string)fk["KeyName"]}]\n"); return(this); }