You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ddlutils-dev@db.apache.org by "Christoffer Hammarström (JIRA)" <ji...@apache.org> on 2006/08/30 14:30:22 UTC
[jira] Created: (DDLUTILS-127) MsSqlBuilder does not drop foreign
keys to a table before dropping the table.
MsSqlBuilder does not drop foreign keys to a table before dropping the table.
-----------------------------------------------------------------------------
Key: DDLUTILS-127
URL: http://issues.apache.org/jira/browse/DDLUTILS-127
Project: DdlUtils
Issue Type: Bug
Components: Core - SqlServer
Reporter: Christoffer Hammarström
Assigned To: Thomas Dudziak
Could not drop object 'Tmp_categories' because it is referenced by a FOREIGN KEY constraint. Query: IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'Tmp_categories')
BEGIN
DECLARE @tablename nvarchar(256), @constraintname nvarchar(256)
DECLARE refcursor CURSOR FOR
SELECT object_name(objs.parent_obj) tablename, objs.name constraintname
FROM sysobjects objs JOIN sysconstraints cons ON objs.id = cons.constid
WHERE objs.xtype != 'PK' AND object_name(objs.parent_obj) = 'Tmp_categories' OPEN refcursor
FETCH NEXT FROM refcursor INTO @tablename, @constraintname
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('ALTER TABLE '+@tablename+' DROP CONSTRAINT '+@constraintname)
FETCH NEXT FROM refcursor INTO @tablename, @constraintname
END
CLOSE refcursor
DEALLOCATE refcursor
DROP TABLE Tmp_categories
END;
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DDLUTILS-127) MsSqlBuilder does not drop foreign
keys to a table before dropping the table.
Posted by "Thomas Dudziak (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/DDLUTILS-127?page=all ]
Thomas Dudziak updated DDLUTILS-127:
------------------------------------
Fix Version/s: 1.0
> MsSqlBuilder does not drop foreign keys to a table before dropping the table.
> -----------------------------------------------------------------------------
>
> Key: DDLUTILS-127
> URL: http://issues.apache.org/jira/browse/DDLUTILS-127
> Project: DdlUtils
> Issue Type: Bug
> Components: Core - SqlServer
> Reporter: Christoffer Hammarström
> Assigned To: Thomas Dudziak
> Fix For: 1.0
>
> Attachments: DDLUTILS-127.patch
>
>
> Could not drop object 'Tmp_categories' because it is referenced by a FOREIGN KEY constraint. Query: IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'Tmp_categories')
> BEGIN
> DECLARE @tablename nvarchar(256), @constraintname nvarchar(256)
> DECLARE refcursor CURSOR FOR
> SELECT object_name(objs.parent_obj) tablename, objs.name constraintname
> FROM sysobjects objs JOIN sysconstraints cons ON objs.id = cons.constid
> WHERE objs.xtype != 'PK' AND object_name(objs.parent_obj) = 'Tmp_categories' OPEN refcursor
> FETCH NEXT FROM refcursor INTO @tablename, @constraintname
> WHILE @@FETCH_STATUS = 0
> BEGIN
> EXEC ('ALTER TABLE '+@tablename+' DROP CONSTRAINT '+@constraintname)
> FETCH NEXT FROM refcursor INTO @tablename, @constraintname
> END
> CLOSE refcursor
> DEALLOCATE refcursor
> DROP TABLE Tmp_categories
> END;
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DDLUTILS-127) MsSqlBuilder does not drop foreign
keys to a table before dropping the table.
Posted by "Christoffer Hammarström (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/DDLUTILS-127?page=all ]
Christoffer Hammarström updated DDLUTILS-127:
---------------------------------------------
Attachment: DDLUTILS-127.patch
This patch seems to fix the problem for me.
> MsSqlBuilder does not drop foreign keys to a table before dropping the table.
> -----------------------------------------------------------------------------
>
> Key: DDLUTILS-127
> URL: http://issues.apache.org/jira/browse/DDLUTILS-127
> Project: DdlUtils
> Issue Type: Bug
> Components: Core - SqlServer
> Reporter: Christoffer Hammarström
> Assigned To: Thomas Dudziak
> Attachments: DDLUTILS-127.patch
>
>
> Could not drop object 'Tmp_categories' because it is referenced by a FOREIGN KEY constraint. Query: IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'Tmp_categories')
> BEGIN
> DECLARE @tablename nvarchar(256), @constraintname nvarchar(256)
> DECLARE refcursor CURSOR FOR
> SELECT object_name(objs.parent_obj) tablename, objs.name constraintname
> FROM sysobjects objs JOIN sysconstraints cons ON objs.id = cons.constid
> WHERE objs.xtype != 'PK' AND object_name(objs.parent_obj) = 'Tmp_categories' OPEN refcursor
> FETCH NEXT FROM refcursor INTO @tablename, @constraintname
> WHILE @@FETCH_STATUS = 0
> BEGIN
> EXEC ('ALTER TABLE '+@tablename+' DROP CONSTRAINT '+@constraintname)
> FETCH NEXT FROM refcursor INTO @tablename, @constraintname
> END
> CLOSE refcursor
> DEALLOCATE refcursor
> DROP TABLE Tmp_categories
> END;
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Resolved: (DDLUTILS-127) MsSqlBuilder does not drop foreign
keys to a table before dropping the table.
Posted by "Thomas Dudziak (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/DDLUTILS-127?page=all ]
Thomas Dudziak resolved DDLUTILS-127.
-------------------------------------
Resolution: Fixed
This method is not really intended to do that (which is why it is not exposed in the Platform interface).
I've created convenience methods in the Platform for deleting a table/generating the SQL to do that (incl. the foreign keys pointing to it).
> MsSqlBuilder does not drop foreign keys to a table before dropping the table.
> -----------------------------------------------------------------------------
>
> Key: DDLUTILS-127
> URL: http://issues.apache.org/jira/browse/DDLUTILS-127
> Project: DdlUtils
> Issue Type: Bug
> Components: Core - SqlServer
> Reporter: Christoffer Hammarström
> Assigned To: Thomas Dudziak
> Fix For: 1.0
>
> Attachments: DDLUTILS-127.patch
>
>
> Could not drop object 'Tmp_categories' because it is referenced by a FOREIGN KEY constraint. Query: IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'Tmp_categories')
> BEGIN
> DECLARE @tablename nvarchar(256), @constraintname nvarchar(256)
> DECLARE refcursor CURSOR FOR
> SELECT object_name(objs.parent_obj) tablename, objs.name constraintname
> FROM sysobjects objs JOIN sysconstraints cons ON objs.id = cons.constid
> WHERE objs.xtype != 'PK' AND object_name(objs.parent_obj) = 'Tmp_categories' OPEN refcursor
> FETCH NEXT FROM refcursor INTO @tablename, @constraintname
> WHILE @@FETCH_STATUS = 0
> BEGIN
> EXEC ('ALTER TABLE '+@tablename+' DROP CONSTRAINT '+@constraintname)
> FETCH NEXT FROM refcursor INTO @tablename, @constraintname
> END
> CLOSE refcursor
> DEALLOCATE refcursor
> DROP TABLE Tmp_categories
> END;
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DDLUTILS-127) MsSqlBuilder does not drop foreign
keys to a table before dropping the table.
Posted by "Thomas Dudziak (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/DDLUTILS-127?page=comments#action_12455153 ]
Thomas Dudziak commented on DDLUTILS-127:
-----------------------------------------
Could you please post the SQL in the database and the target model that you used ?
Also, please note that DdlUtils won't remove foreign keys automatically. It will only do so if the foreign key is no longer in the target model (though it could certainly improved to generate an error before actually changing the database).
> MsSqlBuilder does not drop foreign keys to a table before dropping the table.
> -----------------------------------------------------------------------------
>
> Key: DDLUTILS-127
> URL: http://issues.apache.org/jira/browse/DDLUTILS-127
> Project: DdlUtils
> Issue Type: Bug
> Components: Core - SqlServer
> Reporter: Christoffer Hammarström
> Assigned To: Thomas Dudziak
> Fix For: 1.0
>
> Attachments: DDLUTILS-127.patch
>
>
> Could not drop object 'Tmp_categories' because it is referenced by a FOREIGN KEY constraint. Query: IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'Tmp_categories')
> BEGIN
> DECLARE @tablename nvarchar(256), @constraintname nvarchar(256)
> DECLARE refcursor CURSOR FOR
> SELECT object_name(objs.parent_obj) tablename, objs.name constraintname
> FROM sysobjects objs JOIN sysconstraints cons ON objs.id = cons.constid
> WHERE objs.xtype != 'PK' AND object_name(objs.parent_obj) = 'Tmp_categories' OPEN refcursor
> FETCH NEXT FROM refcursor INTO @tablename, @constraintname
> WHILE @@FETCH_STATUS = 0
> BEGIN
> EXEC ('ALTER TABLE '+@tablename+' DROP CONSTRAINT '+@constraintname)
> FETCH NEXT FROM refcursor INTO @tablename, @constraintname
> END
> CLOSE refcursor
> DEALLOCATE refcursor
> DROP TABLE Tmp_categories
> END;
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DDLUTILS-127) MsSqlBuilder does not drop foreign
keys to a table before dropping the table.
Posted by "Christoffer Hammarström (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/DDLUTILS-127?page=comments#action_12455261 ]
Christoffer Hammarström commented on DDLUTILS-127:
--------------------------------------------------
I think i was using dropTable() directly, but i'm not sure.
> MsSqlBuilder does not drop foreign keys to a table before dropping the table.
> -----------------------------------------------------------------------------
>
> Key: DDLUTILS-127
> URL: http://issues.apache.org/jira/browse/DDLUTILS-127
> Project: DdlUtils
> Issue Type: Bug
> Components: Core - SqlServer
> Reporter: Christoffer Hammarström
> Assigned To: Thomas Dudziak
> Fix For: 1.0
>
> Attachments: DDLUTILS-127.patch
>
>
> Could not drop object 'Tmp_categories' because it is referenced by a FOREIGN KEY constraint. Query: IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'Tmp_categories')
> BEGIN
> DECLARE @tablename nvarchar(256), @constraintname nvarchar(256)
> DECLARE refcursor CURSOR FOR
> SELECT object_name(objs.parent_obj) tablename, objs.name constraintname
> FROM sysobjects objs JOIN sysconstraints cons ON objs.id = cons.constid
> WHERE objs.xtype != 'PK' AND object_name(objs.parent_obj) = 'Tmp_categories' OPEN refcursor
> FETCH NEXT FROM refcursor INTO @tablename, @constraintname
> WHILE @@FETCH_STATUS = 0
> BEGIN
> EXEC ('ALTER TABLE '+@tablename+' DROP CONSTRAINT '+@constraintname)
> FETCH NEXT FROM refcursor INTO @tablename, @constraintname
> END
> CLOSE refcursor
> DEALLOCATE refcursor
> DROP TABLE Tmp_categories
> END;
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira