Dyntex.net Support Centre: MySQL Reference Manual p.516

<< previous page -- table of contents -- next page >>

Chapter_1

Chapter_2

Chapter_3

Chapter_4

Chapter_5

Return to Table of Contents

Table of Contents
1. General Information
2. MySQL Installation
3. Tutorial Introduction
4. Database Administration
5. MySQL Optimisation
6. MySQL Language Reference
7. MySQL Table Types
8. MySQL APIs
9. Extending MySQL

516 MySQL Technical Reference for Version 4.0.3 constraints: Any ALTER TABLE removes all foreign key constrainst dened for the table. You should not use ALTER TABLE to the referenced table either, but use DROP TABLE and CREATE TABLE to modify the schema. When MySQL does an ALTER TABLE it may internally use RENAME TABLE, and that will confuse the foreign key costraints which refer to the table. A CREATE INDEX statement is in MySQL processed as an ALTER TABLE, and these restrictions apply also to it. When doing foreign key checks InnoDB sets shared row level locks on child or parent records it has to look at. InnoDB checks foreign key constraints immediately: the check is not deferred to transaction commit. InnoDB allows you to drop any table even though that would break the foreign key con- straints which reference the table. When you drop a table the constraints which were dened in its create statement are also dropped. If you re-create a table which was dropped, it has to have a denition which conforms to the foreign key constraints referencing it. It must have the right column names and types, and it must have indexes on the referenced keys, as stated above. If these are not satised, MySQL returns error number 1005 and refers to errno 150 in the error message string. Starting from version 3.23.50 InnoDB returns the foreign key denitions of a table when you call SHOW CREATE TABLE yourtablename Then also `mysqldump' produces correct denitions of tables to the dump le, and does not forget about the foreign keys. You can also list the foreign key constraints for a table T with SHOW TABLE STATUS FROM yourdatabasename LIKE 'T' The foreign key constraints are listed in the table comment of the output. 7.5.5 Adding and Removing InnoDB Data and Log Files From version 3.23.50 and 4.0.2 you can specify the last InnoDB datale to autoextend. Alternatively, you can increase to your tablespace by specifying an additional datale. To do this you have to shut down the MySQL server, edit the `my.cnf' le adding a new datale to innodb_data_file_path, and then start the MySQL server again. Currently you cannot remove a datale from InnoDB. To decrease the size of your database you have to use `mysqldump' to dump all your tables, create a new database, and import your tables to the new database. If you want to change the number or the size of your InnoDB log les, you have to shut down MySQL and make sure that it shuts down without errors. Then copy the old log les into a safe place just in case something went wrong in the shutdown and you will need them to recover the database. Delete then the old log les from the log le directory, edit `my.cnf', and start MySQL again. InnoDB will tell you at the startup that it is creating new log les.

MySQL Reference Manual

Our Partners

SpiritHost - web hosting for spiritual and education sites
SpiritHit.com - Religious and Spiritual Portal

Return to Table of Contents	Back to top

Web Hosting: Manuals & FAQ's

1. Unix-Based Web Hosting
2. Unix Dedicated Servers
3. Windows Dedicated Servers
4. CuteFTP User’s Guide
5. CuteHTML User’s Guide
6. WS_FTP Pro User's Guide
7. Miva Order User's Guide
8. Miva Merchant User's Guide