Change Table Name?

[Still Learning]

Is it possible to change a table name using the schema design interface?

Also, using schema designer where does one set the default table properties (engine) and/or column properties (specifically I imported a schema from mysql 5.0 and now have some integer values set at length 10 and some set at 11 — which is best to use for a PK?)

[peterlaursen]

1) Is it possible to change a table name using the schema design interface?

>> Currently not. You will have to do from Object Browser or by executing 'RENAME TABLE'. Refreshing Schema Designer will remove the table as the name has changed. You will need to add again.

2) Setting default charset and engine for tables is behind the 'advanced' button of CREATE TABLE dialogue

3) It is other considerations that decide which one should be PK and which should be indexed otherwise. Those that you use for Foreign Keys, JOINs, ORDER BY and GROUP BY should be indexed somehow if not tables are very small. Tuning indexes with 'big and busy' databases is a study in itself!

[Still Learning]

Thanks for your prompt reply, using RENAME in the object browser works well enough (though it requires a refresh and series of clicks to show up on the schema).

However, a major prob I am coming across is that when I add a new table it refuses to let me make relationships with/to it claiming a “Table Mis-match. Please refresh the canvas”. Yet the prob does not go away even after I refresh, nor after I change both Tables Tabletype to InnoDB (via the right hand button); nor after I then refresh again etc.

As such, 1.) Is there any suggestions you have to address the above? 2.) Is there anyway to set the default table type (ie. when creating a new table) to InnoDB (or another type) so this issue never becomes a factor?

Thanks in advance

Mike

[peterlaursen]

“I add a new table it refuses to let me make relationships with/to it claiming a “Table Mis-match. Please refresh the canvas” “

I have not encountered that! But we can check with the code of course what exactly condition raises exatly this error mesage!

1) Can you describe in detail how to reproduce it?

2) Does save SCHEMA .. program restart .. open SCHEMA remove this message? Does close and reopen connection?

[Still Learning]

“I add a new table it refuses to let me make relationships with/to it claiming a “Table Mis-match. Please refresh the canvas” “

I have not encountered that! But we can check with the code of course what exactly condition raises exatly this error mesage!

1) Can you describe in detail how to reproduce it?

I have a schema I imported from mysql 5. I create a new table (via right click on schema background). I try to drag and drop a column to or from the new table to an old table (either way) but am greeted with “Table mismatch. Please refresh the canvas”. I try refreshing via F5 and/or via right click on canvas. No joy. I create two new tables. I try to drag and drop from one of the new tables to another but get the same error message. (((Is there a way to refresh in the object view or ????)

2) Does save SCHEMA .. program restart .. open SCHEMA remove this message? NO

Does close and reopen connection? NO

[peterlaursen]

“I try to drag and drop a column to or from the new table to an old table”

Moving columns from one table to another is not supported with the Schema Designer.

We had a similar request and it is added here allready: http://code.google.com/p/sqlyog/issues/detail?id=451

.. but absolutely not without problems to implement.

What about indexes defined on that column? or Foreign Keys referencing it?

What if tables have different default charsets and the charset/collation is not explicitly defined for the column?

Also would you expect data to be copied with that operation?

This would really take some operations on the background!

[Still Learning]

Sorry, I meant, drag one column (with exactly matching attributes) over top of another table's column in order to create a FK reference

What about indexes defined on that column? or Foreign Keys referencing it?

>>>>>>I've played with each of these settings without luck as yet

What if tables have different default charsets and the charset/collation is not explicitly defined for the column?

>>>>>>> The only thing notable I can find is that when I right click >> View advanced properties on any of the new tables there is no info displayed whatsoever whereas the same process on existing tables lists a great deal of info (however, double click to open either new or old table and then clicking on advanced properties lists EXACTLY the same attributes for each table.

[peterlaursen]

ok .. you are creating a FK – sorry I did not realize this!

the *Table Mismatch* errror can occur if LETTERCASE do not match.

Currently SD does not support both 'Table' and 'table' when connected to a Linux server.

We also find an issue whne uppercases are used on a Windows seerver (and that is definitely a bug!).

What is the server platform?

What is the 'lower_case_table_names' setting for this server ?

[peterlaursen]

I am told that I was mistaken as regards Linux 'tables' and 'TABLES' ..

But LETTERCASE mismatch may occur on Windows.

[Still Learning]

IMPORTANT FOR NEWBIES

After much frustration (mostly due to my own inexperience), I've “discovered” the issues alluded to here. FWIW sqlyog is turning out to be a great tool (frankly I don't know how anyone ever would administer a dbase without a similar tool) even if the aforementioned TABLE NAMES USING CamelCaps WITH MYSQL RUNNING ON WINDOWS proved to be a “bugger” of a bug (i.e. make sure you name/rename the tables all lowercase to use this product with mysql running on a windows machine).

Moreover, be aware you must create your own index (and in the proper order) to create some FK relationships (I don't know if this is a requirement of mysql or even a best practice), as well as making all the attributes the same in BOTH the two fields being related.

While I'm not an expert by any means, I hope this helps any and all . . .

[peterlaursen]

The issue with Windows servers having 'lower_case_table_names' setting = 2 (allowing for using UPPERCASES) will be solved soon of course!

[Author]

Posts