|
From: Ben S. <bxs...@ya...> - 2013-05-22 07:56:56
|
Ok, thank you. Regarding the DROP TABLE command one more question: Is there an advanced DROP command which allow me to enforce a drop a table WITH all depending objects? Or is this not possible because I always have to specify this already at creation time e.g. with a CREATE TABLE .... ON CASCADING DELETE; option? How do I find out which objects (INDEXES...) depend from a TABLE? Ben --Original Message Text--- From: Cristiano Guadagnino Date: Tue, 14 May 2013 09:30:31 +0200 Hi Ben! The problem in your DDL script is the name of the index. This is your script: CREATE UNIQUE INDEX PRIMARY ON tbl_test2(id) This means you want to create an index named "PRIMARY" on the table tbl_test2. But an index named "PRIMARY" already exists on the table tbl_test. Tipically the name of indexes must be unique at the database level, so you have to change the name of the new index. Regarding your DROP instruction, the problem is that you have referential integrity constraints on that table. The table is a parent in the constraint, so you cannot drop it. You should learn a bit on referential integrity to understand what's going on. Basically, you have a link between one of more child table and a parent table, so that some of the data in the child tables depend on the data present in the parent table. When you create those links, you can specify to the DBMS what it should do when someone tries to invalidate the link (e.g. by deleting one row in the parent table). You have various possible actions; one of those actions is to simply refuse deleting or dropping the object. Obviously you can force it, at the risk of trashing your data, so I'll let it to you to discover how ;-) Hope this helps. Bye Cris On Tue, May 14, 2013 at 8:00 AM, Ben Stover <bxs...@ya...> wrote: I connected successfully to an MySQL database then opened the OBJECTS tree, right clicked on a certain existing table and selected Scripts->Create Table Script As result the following DDL code is generated in SQL editor: > CREATE TABLE tbl_test > ( > id bigint PRIMARY KEY NOT NULL, > TOP_CHANGED_AT timestamp, > TOP_CHANGED_BY varchar(255), > TOP_CREATED_AT timestamp, > TOP_CREATED_BY varchar(255), > TOP_FINAL_TIMEOUT int > ) > ; > CREATE UNIQUE INDEX PRIMARY ON tbl_test(id) > ; Now I simply change the table name and append a "2", then I let the script run: >CREATE TABLE tbl_test2 > ( > id bigint PRIMARY KEY NOT NULL, > TOP_CHANGED_AT timestamp, > TOP_CHANGED_BY varchar(255), > TOP_CREATED_AT timestamp, > TOP_CREATED_BY varchar(255), > TOP_FINAL_TIMEOUT int > ) > ; >CREATE UNIQUE INDEX PRIMARY ON tbl_test2(id) > ; Much to my surprise I got errors: Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PRIMARY ON tbl_test2(id)' at line 1 SQLState: 42000 ErrorCode: 1064 Error occured in: CREATE UNIQUE INDEX PRIMARY ON tbl_test2(id) So Squirrels seems NOT to produce valid DDL CREATE TABLE scripts from existing tables.. Whats wrong? Furthermore if I try to execute a simple DROP TABLE tbl_test; I got the following error: Error: Cannot delete or update a parent row: a foreign key constraint fails SQLState: 23000 ErrorCode: 1217 Why is it impossible to drop the existing table (including index)? I am using MySQL 64bit MySQL v5.6.10 Ben ------------------------------------------------------------------------------ AlienVault Unified Security Management (USM) platform delivers complete security visibility with the essential security capabilities. Easily and efficiently configure, manage, and operate all of your security controls from a single console and one unified framework. Download a free trial. http://p.sf.net/sfu/alienvault_d2d _______________________________________________ Squirrel-sql-users mailing list Squ...@li... https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users |
