• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Export database with constraints at the end of backup file

#1
Hello,
I want to make a database export with constraints of the tables, but with constraints written at the end of the backup file.
I use MySQL database.
I use $this->dbutil->backup($prefs) but in array $prefs, is any parameter to say "put constraints at the end of backup file" ?
.

For the moment, i have for eaxmple in the backup file :
PHP Code:
//--- first table
CREATE TABLE alpha
..
PRIMARY KEY (`id`),
KEY id1 , ...
CONSTRAINT ... FOREIGN KEY ...

INSERT INTO .....

//-- second table 
CREATE TABLE beta
...
PRIMARY KEY (`id`),
KEY id1 , ...
CONSTRAINT ... FOREIGN KEY ...

INSERT INTO .....

// etc... 
The problem is when i want to restore my database with this export, because of the foreign keys.
I want to make an export like that foreach table :
- first create table
- insert into table
Only when this is ended, add foreign constraints key on table.

PHP Code:
// first 
CREATE TABLE alpha
...
PRIMARY KEY (`id`),
KEY id1 , ...

INSERT INTO ...

//--- second
CREATE TABLE beta 
...
PRIMARY KEY (`id`),
KEY id1 , ...

INSERT INTO ...

//-- etc...
//-- end of bakcup file, the constraints
--
-- 
Constraint for table alpha
--
ALTER TABLE alpha
  ADD CONSTRAINT 
... FOREIGN KEY (`..`) REFERENCES `.. ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE beta
  ADD CONSTRAINT ... FOREIGN KEY (
`..`) REFERENCES `.. ON DELETE CASCADE ON UPDATE CASCADE;
 
   ADD CONSTRAINT ... FOREIGN KEY ..  REFERENCES ... ON UPDATE CASCADE,
 
 ADD CONSTRAINT `deptcategorie_ibfk_3FOREIGN KEY (`iddept`) REFERENCES `departement` (`iddept`) ON UPDATE CASCADE,
 
 ADD CONSTRAINT `deptcategorie_ibfk_5FOREIGN KEY (`idcategorie`) REFERENCES `categorie` (`idcategorie`) ON UPDATE CASCADE

Thank you for your help
Reply

#2
This is an important question, I too would like to know a solution. I just discovered that the intelligent backups created every day by my applications aren't immediately usable for restore.
I need to decompress them, edit them moving the constraint to the bottom, often re-compress, and try the upload...
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.