What are the challenges you face when working across database platforms? Take the survey
Options

Cannot drop index (SQL compare 3.1.7.218)

dynamic-imagingdynamic-imaging Posts: 3
edited November 28, 2005 7:08AM in SQL Compare Previous Versions
We've realized that it is not possible to synchronize 2 databases that differe in one unique index on one table that is used to check foreign key constraint defined on the other table. Drop index command generated by the SQL Compare tool fails because of the foreign key check.
Please, pay attention that table with unque index is unusual due to both primary key and unique index defined on the same attribute.

+++ SCHEMA of the database to be modified

CREATE TABLE AUDIT_ACTIONS
(
NCDACTION numeric(18, 0) NOT NULL ,
ACTIONNAME varchar (120)
)
create unique index IX_AUDIT_ACTIONS on dbo.AUDIT_ACTIONS
(NCDACTION)
ALTER TABLE AUDIT_ACTIONS ADD CONSTRAINT PK_AUDIT_ACTIONS PRIMARY KEY CLUSTERED (NCDACTION)

CREATE TABLE LOGGED_EVENT
(
NCDEVENT UID IDENTITY (1, 1) NOT NULL ,
NCDACTION numeric(18, 0) NOT NULL ,
EVENTDATETIME datetime NOT NULL ,
NCDUSER numeric(18, 0) NOT NULL ,
COMMENT varchar (1000)
)
ALTER TABLE LOGGED_EVENT ADD CONSTRAINT PK_LOGGED_EVENT PRIMARY KEY CLUSTERED (NCDEVENT)
ALTER TABLE LOGGED_EVENT ADD CONSTRAINT FK_LOGGED_E_REFERENCE_AUDIT_AC FOREIGN KEY (NCDACTION) REFERENCES AUDIT_ACTIONS (NCDACTION)

+++ SCHEMA of the new reference database

CREATE TABLE AUDIT_ACTIONS
(
NCDACTION numeric(18, 0) NOT NULL ,
ACTIONNAME varchar (120)
)
/* NO INDEX ANYMORE
create unique index IX_AUDIT_ACTIONS on dbo.AUDIT_ACTIONS
(NCDACTION)
*/
ALTER TABLE AUDIT_ACTIONS ADD CONSTRAINT PK_AUDIT_ACTIONS PRIMARY KEY CLUSTERED (NCDACTION)

CREATE TABLE LOGGED_EVENT
(
NCDEVENT UID IDENTITY (1, 1) NOT NULL ,
NCDACTION numeric(18, 0) NOT NULL ,
EVENTDATETIME datetime NOT NULL ,
NCDUSER numeric(18, 0) NOT NULL ,
COMMENT varchar (1000)
)
ALTER TABLE LOGGED_EVENT ADD CONSTRAINT PK_LOGGED_EVENT PRIMARY KEY CLUSTERED (NCDEVENT)
ALTER TABLE LOGGED_EVENT ADD CONSTRAINT FK_LOGGED_E_REFERENCE_AUDIT_AC FOREIGN KEY (NCDACTION) REFERENCES AUDIT_ACTIONS (NCDACTION)

Comments

  • Options
    Hi,

    I was able to synchronize the two databases successfully, but I hadn't included the UID datatype that you use in your tables (I'd changed this to int).

    It either has to do with the UDDT adding a bit of complexity, or you've got an out-of-date version of the software.

    Can you please do a chack for updates from the Help menu and make sure you've got the latest version of SQL Compare? If you've already got it, please give me some information about the UID user-defined datatype so I can add it to the database and try to replicate the problem.

    Thanks!
  • Options
    Hi,
    thank you for the promptly reply and sorry for the inconvinience. There is one mismatch from actual databases in the scripts I have provided before. Old database has index, but no primary key. Please try new scripts.

    +++ SCHEMA of the database to be modified

    CREATE TABLE AUDIT_ACTIONS
    (
    NCDACTION numeric(18, 0) NOT NULL ,
    ACTIONNAME varchar (120)
    )
    create unique index IX_AUDIT_ACTIONS on dbo.AUDIT_ACTIONS
    (NCDACTION)
    /* NO PRIMARY KEY BEFORE
    ALTER TABLE AUDIT_ACTIONS ADD CONSTRAINT PK_AUDIT_ACTIONS PRIMARY KEY CLUSTERED (NCDACTION)
    */
    CREATE TABLE LOGGED_EVENT
    (
    NCDEVENT numeric(18,0) IDENTITY (1, 1) NOT NULL ,
    NCDACTION numeric(18, 0) NOT NULL ,
    EVENTDATETIME datetime NOT NULL ,
    NCDUSER numeric(18, 0) NOT NULL ,
    COMMENT varchar (1000)
    )
    ALTER TABLE LOGGED_EVENT ADD CONSTRAINT PK_LOGGED_EVENT PRIMARY KEY CLUSTERED (NCDEVENT)
    ALTER TABLE LOGGED_EVENT ADD CONSTRAINT FK_LOGGED_E_REFERENCE_AUDIT_AC FOREIGN KEY (NCDACTION) REFERENCES AUDIT_ACTIONS (NCDACTION)

    +++ SCHEMA of the new reference database

    CREATE TABLE AUDIT_ACTIONS
    (
    NCDACTION numeric(18, 0) NOT NULL ,
    ACTIONNAME varchar (120)
    )
    /* NO INDEX ANYMORE
    create unique index IX_AUDIT_ACTIONS on dbo.AUDIT_ACTIONS
    (NCDACTION)
    */
    ALTER TABLE AUDIT_ACTIONS ADD CONSTRAINT PK_AUDIT_ACTIONS PRIMARY KEY CLUSTERED (NCDACTION)

    CREATE TABLE LOGGED_EVENT
    (
    NCDEVENT numeric(18, 0) IDENTITY (1, 1) NOT NULL ,
    NCDACTION numeric(18, 0) NOT NULL ,
    EVENTDATETIME datetime NOT NULL ,
    NCDUSER numeric(18, 0) NOT NULL ,
    COMMENT varchar (1000)
    )
    ALTER TABLE LOGGED_EVENT ADD CONSTRAINT PK_LOGGED_EVENT PRIMARY KEY CLUSTERED (NCDEVENT)
    ALTER TABLE LOGGED_EVENT ADD CONSTRAINT FK_LOGGED_E_REFERENCE_AUDIT_AC FOREIGN KEY (NCDACTION) REFERENCES AUDIT_ACTIONS (NCDACTION)
  • Options
    Thanks for that, Vitaly.

    It looks like it could be a bug in the software. We'll see if we can get it fixed in time for the next release.
  • Options
    Hi,

    I'm happy to say that this doesn't happen in the latest build, so the next release (proverbial 3-4 weeks) will fix it.
  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    This has indeed been fixed.
This discussion has been closed.