[AlanT]

navyashree.r wrote on May 8 2009, 02:27 PM:

Hi,

To change the sequence of columns in a table, select the table in the Object Browser and select Reorder Columns from the popup menu or Table menu — Reorder column(s) or just press Ctrl+ALt+R.

Select any column and click Up or Down button to move the column up/down. Having re-sequenced the columns, click Re-Order to save the table with changed structure. The column data in the table gets re-arranged accordingly.

See image “reorder.JPG”

For more info read Help doc. Thank You.

Regards,

Navya

Yeah, thanks 🙂

[AlanT]

peterlaursen wrote on Apr 23 2009, 12:51 PM:

It is *not* commented! You should know the difference in MySQL between

/* …. */

and (the difference is the expression mark)

/*! …. */

Refer to http://dev.mysql.com/doc/refman/5.0/en/comments.html. They give the example

This means that with version 3.23.02 and higher it is *not* a comment – with versions before 3.23.02 it is a comment! This is the way in MySQL to handle the situation when different server versions require different syntax or when an old serv er does not support a construction supported by later versions. In this case it is required because before 3.23.02 MySQL did not support temporary tables.

OK, got it.

Thanks.

[AlanT]

nithin wrote on Apr 22 2009, 09:44 PM:

Hi,

I think still this script got corrupted a bit. Please confirm once again the latest script you attached that you didn't edit anything?

Please see this line in the latest script

I can't see column 'RegionCode' in table 'location' ( 'location' has got a PK column 'LocationCode')

While importing, table 'location' is created before 'station'. Since the parent table ('location') present without column 'RegionCode', the creation of 'station' table fails if you specify CONSTRAINT with a non-existing column of 'location' table.

Of course if 'station' table comes prior to the 'location' table in the script, the table 'station' will be created successful with 'SET FOREIGN_KEY_CHECKS = 0' on top of the script (But still 'location' will fail this time). But that kind of reordering is not required here if you would not have edited the script manually.

And I changed the above CONSTRAINT line like this and will be able to import the script successfully.

So you also can try this once.

I also suggest do a fresh EXPORT with 'SET FOREIGN_KEY_CHECKS = 0' option selected, and there is no possibility of failing 'Import' process as far I know.

Let me know still if you have any issue?

Thanks

Nithin

Yes, I think I did not rectify the relationship in the table 'station'.

Now I can also run the script without problem. Thanks.

Go back to the 'SET FOREIGN_KEY_CHECKS = 0',

I noticed this is 'commented' at the top of the script, how come this has effect on the foreign key relationship?

If this is 'commented', it should have problem because table 'station' is created before the lookup table 'stationtype' (at the bottom of the script).

[AlanT]

navyashree.r wrote on Apr 22 2009, 03:37 PM:

Hi,

Please confirm whether have you edited manually the SQL dump which u have attached.

It has only Insert statement but not the CREATE TABLE Structure for “LOCATION” table. So, please attach fresh dump using Tools –> Backup as SQL dump which helps to verify at our end.

Thank You in advance.

Regards,

Navya

Yes, I use 'Backup as SQL Dump' and manually deleted many other tables.

I have added the CRETAE TABLE for Location now.

Sorry for the mistake.

[AlanT]

nithin wrote on Apr 21 2009, 07:33 PM:

SET FOREIGN_KEY_CHECKS = 0 option doesn't make any change in order in the script except it adds a statement SET FK_CHECKS = 0 on top of the script which handles the Table dependencies while importing ('Restore From SQL Dump) the script.

I repeat, While exporting we are not checking whether its lookup table or primary table. We add the statements to script in the order the MySQL returns result for “show full tables” (Usually MySQL returns table names in Alphabetical order only).

Please confirm after checking the 'SET FOREIGN_KEY_CHECKS = 0' option 'Import process' is failing or not? If its failing you may need to attach the SQL dump so that we can reproduce at our end and solve quickly.

Thanks

Nithin

Hi,

Please see my attached script which was failed when I did a 'Restore'.

Thanks

[AlanT]

navyashree.r wrote on Apr 21 2009, 05:03 PM:

Hi AlanT,

We should check the option SET FK_CHECKS = 0 while exporting if Foreign key is present. This avoids an error when importing or restoring the SQL dump. See image “sqldump.JPG”.

Have you checked this option please make sure?

While exporting we are not checking whether its lookup table or primary table. We add the statements to script in the order the MySQL returns result for “show full tables”.

Regards,

Navya

Hi,

Yes, I did check the box “SET FK_CHECKS = 0”.

I just tried to generate the script again.

The generated sql script is in alphabetical order.

Although I checked the option “SET FK_CHECKS = 0”, I did not find anything different in the script that allows to create primary table before lookup table.

Any idea?

[AlanT]

AlanT wrote on Apr 21 2009, 08:54 AM:

I generated the script with 'Structure and Data', when I import the script in my home pc, it got the error because the 'primay' table 'Employee' script is before the lookup table 'Position'. That is it cannot insert the records into 'Employee' table before the 'Position', it is due to the Foreign key constraint.

So I need to place the script generating the 'Position' and the insert data script first.

In addition, I noticed the script generated was in alphabetical order according to the table names.

[AlanT]

I generated the script with 'Structure and Data', when I import the script in my home pc, it got the error because the 'primay' table 'Employee' script is before the lookup table 'Position'. That is it cannot insert the records into 'Employee' table before the 'Position', it is due to the Foreign key constraint.

So I need to place the script generating the 'Position' and the insert data script first.

[Author]

Posts