Constraint Exists Check

[William Floor]

Before dropping a constraint, it would be handy if we can check if it exists. This is especially usefull when you start using migrations on an already existing database but due to historical reasons we cant garantee the constraint exists (i.e. a foreign key).

It would be great if there can be a method added like Schema.Constraint(name).exists(). Or is there another neat way to do it?

Or an ugly way? :)

[Zidad]

Hi William,

Are you using SQL Server? If yes, I have some extension methods that do just that:

https://github.com/zidad/net-tools/blob/master/src/Net.FluentMigrator/FluentMigrationSyntaxExtensions.cs

See the ForeignKeyExists, TableConstraintExists etc...

Let me know if you're running into any issues!

- Wiebe

[Zidad]

I turned the previously mentioned code into a nuget package if you don't want to copy all the source.

Install-Package Net.FluentMigrator

Cheers!

Wiebe 

On Friday, January 24, 2014 3:58:49 PM UTC+1, William Floor wrote:

[William Floor]

Thanks, this can be very usefull. We need to be able to support MSSQL as well as Oracle. But with this example I can make these extensions myself. I did not know where to start with this.

My first idea was to just use a simple dbcommand to do the trick, but this is a very nice way!

Op maandag 27 januari 2014 10:40:37 UTC+1 schreef Zidad:

[William Floor]

Why isnt the SchemaConstraintQuery class implemented, just ilke the SchemaIndexQuery class?

i.e.:

ublic SchemaConstraintQuery(string schemaName, string tableName, string constraintName, IMigrationContext context)

        {

            _schemaName = schemaName;

            _tableName = tableName;

            _constraintName = constraintName;

            _context = context;

        }

        public bool Exists()

        {

            return _context.QuerySchema.ConstraintExists(_schemaName, _tableName, _constraintName);

        }

Op vrijdag 24 januari 2014 15:58:49 UTC+1 schreef William Floor:

[Zidad]

Uhm Well probably I just didn't know about it when I wrote it, or it might have not been around yet (I think I wrote this code 2 years ago already)

The code worked fine for my purpose, as I wanted an easy way to check for the existence of data, and it was very to extend this with for schema queries.

But you're very welcome to submit a pull request :)

Wiebe

[William Floor]

Okay, so there is not really a reason why it was not implemented :)

As soon as I've tested it and read how this pull request stuff works ;) I'll put in a request. Thanks.

Op vrijdag 24 januari 2014 15:58:49 UTC+1 schreef William Floor:

Before dropping a constraint, it would be handy if we can check if it exists. This is especially usefull when you start using migrations on an already existing database but due to historical reasons we cant garantee the constraint exists (i.e. a foreign key).

[Wiebe]

By 'when I wrote that' I mean the code in my repository of course, not the one in FluentMigrator...

[William Floor]

Well,

I've forked the fluentmigrator git, and added the contraint syntax and tested it. Works very well, so I did a pull request anyway :)

Op vrijdag 31 januari 2014 13:56:08 UTC+1 schreef Wiebe:

[BobC5]

I'm unsuccessfully trying to guess how to use ForeignKeyExists.

I'd appreciate a code sample showing it in use.

[Tom Marien]

Hi Bob

Suppose you have an account table where you want to have a foreignkey to:

Create.Table("TableWithForeignkeytoAccount")
                  .WithColumn(PrimaryKeyName).AsGuid().PrimaryKey().NotNullable().WithDefault(SystemMethods.NewGuid)
                  .WithColumn("ACCOUNT_ID").AsInt32().Nullable().ForeignKey("FK_ACCOUNT", "ACCOUNT", "ID").OnDelete(Rule.Cascade)

I hope this helps

Kind regards

Tom Marien

[Wiebe Tijsma]

Hi Bob,

Yes there's some trickery to get the data context, can you try this:

public class AddGroupNameColumn : ForwardOnlyMigration

{

    public override void Up()

    {

        if (!this.GetData().ForeignKeyExists("FK_MyForeignKeyName"))

        {

            Execute.EmbeddedScript("CreateForeignKey.sql");

        }

    }

}

But you'll need some of the other dependencies from here too (available on nuget too, but it might just be as easy to include the code in your project):

https://github.com/zidad/net-tools/tree/master/src/Net.FluentMigrator

Wiebe 

--
You received this message because you are subscribed to a topic in the Google Groups "FluentMigrator Google Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/fluentmigrator-google-group/euBgGm7z4Oc/unsubscribe.
To unsubscribe from this group and all its topics, send an email to fluentmigrator-goog...@googlegroups.com.
To post to this group, send email to fluentmigrato...@googlegroups.com.
Visit this group at http://groups.google.com/group/fluentmigrator-google-group.

For more options, visit https://groups.google.com/d/optout.

[BobC5]

Thank you very much for your help and for this useful set of extensions to FluentMigrator.

I was able to get it to work after pulling a number of extension methods from some of your other projects.

I'm sure this would have been much simpler if I used your NuGet package, but I wanted to use source code.

To unsubscribe from this group and all its topics, send an email to fluentmigrator-google-group+unsub...@googlegroups.com.

To post to this group, send email to fluentmigrato...@googlegroups.com.
Visit this group at http://groups.google.com/group/fluentmigrator-google-group.

[Wiebe Tijsma]

Sure, welcome!

Feel free to submit a pull if make any useful additions/fixes, now it's just tailored to what I needed specifically...

Wiebe

To unsubscribe from this group and all its topics, send an email to fluentmigrator-goog...@googlegroups.com.

To post to this group, send email to fluentmigrato...@googlegroups.com.
Visit this group at http://groups.google.com/group/fluentmigrator-google-group.

For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to a topic in the Google Groups "FluentMigrator Google Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/fluentmigrator-google-group/euBgGm7z4Oc/unsubscribe.

To unsubscribe from this group and all its topics, send an email to fluentmigrator-goog...@googlegroups.com.