6 Replies Latest reply on Nov 11, 2011 8:36 AM by MattHirons

    Modify UNIQUE field validation option with SQL?

    MattHirons

      Title

      Modify UNIQUE field validation option with SQL?

      Post

      I am using the 2empowerFM SQL Runner plugin to execute SQL statements against my FM database.  I am attempting to create a script that will re-index certain fields.

      This works for fields that are not identified as UNIQUE:

      DROP INDEX ON Table.Field

      CREATE INDEX ON Table.Field

       

      However, for UNIQUE fields, you can't drop the index with the UNIQUE option specified.  You have to remove the UNIQUE option, drop the index, add the index back on, and then re-specify the UNIQUE option.

      So, my question is: What SQL statement can I use to add/remove only the UNIQUE option?

      I was hoping I could use something like this, but I can't seem to get it to work:

      ALTER TABLE Table ALTER Field DROP UNIQUE

      ALTER TABLE Table ALTER Field ADD UNIQUE

       

      Does anyone have any experience with this?  Or are there other ways of accomplishing it?

       

      Thanks,

      Matt