|
From: John H. <jh...@im...> - 2013-05-22 14:32:06
|
On Wed, 22 May 2013, Ben Stover wrote: > 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? That would be platform-dependent. Check your DBMS SQL syntax documentation. Generally speaking, no, it's not likely. The easier it is to automate destroying things, the easier it is to accidentally destroy things you don't want destroyed. > How do I find out which objects (INDEXES...) depend from a TABLE? Again, platform-dependent. However, Squirrel does provide metadata (schema) information on one of the tabs. Look there. I don't know if that includes FKs, I'm not in a position to check at the moment. > > --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 > > > > > > > > -- John Hardin KA7OHZ http://www.impsec.org/~jhardin/ jh...@im... FALaholic #11174 pgpk -a jh...@im... key: 0xB8732E79 -- 2D8C 34F4 6411 F507 136C AF76 D822 E6E6 B873 2E79 ----------------------------------------------------------------------- Windows Genuine Advantage (WGA) means that now you use your computer at the sufferance of Microsoft Corporation. They can kill it remotely without your consent at any time for any reason; it also shuts down in sympathy when the servers at Microsoft crash. ----------------------------------------------------------------------- 5 days until Memorial Day - honor those who sacrificed for our liberty |
