|
From: Cristiano G. <cri...@gm...> - 2013-05-14 07:30:59
|
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 > |
