Cannot drop index (SQL compare 3.1.7.218)
dynamic-imaging
Posts: 3
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)
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)
This discussion has been closed.
Comments
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!
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)
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.
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.