Code Blog

Displaying 1-2 of 2 results.
2015/05/15 Yii,MySQL

MySQL statements for table dependences 

ALTER TABLE tbl_posttag 
ADD CONSTRAINT fk_codeblog_id 
FOREIGN KEY (post_id) REFERENCES tbl_codeblog (id) ON DELETE CASCADE;
 
ALTER TABLE tbl_posttag 
ADD CONSTRAINT fk_tag_id 
FOREIGN KEY (tagId) REFERENCES tbl_tag (id) ON DELETE CASCADE;
 
However, its easy to run into issues when creating dependences. In that case run this command directly after the error for more detailed information:
SHOW ENGINE INNODB STATUS;

Print the CREATE statement for a certain table is also useful for debugging:
CREATE INDEX CODEBLOG_ID_INDEX ON tbl_codeblog (id);

You need to check that all keys are of exactly the same type (length, unsigned, character encoding etc). The key also needs to be indexed. The table also needs to be InnoDB.
I also had a problem with a table inconsistency. I had already created a mapping between a post and tag in tbl_posttag, but the tag entry had already been deleted. In this case I cannot create the dependency without first removing the incorrect posttag entry.


2015/05/07 Yii,MySQL

I unfortunately had made some mistakes in my Yii code, which meant I had to restore only certain values from an old copy of my database. Normally it would be easiest to just copy over the entire database from the backup, but my backup wasn't up-to-date will all the data.
Basically the old backup had correct "create_time" for old posts, but as I also had new posts still not in the backup, so I therefore wanted to just extract the values for the "postid" that existed in both databases and after that update the "create_time" from the backup. But the process would be the same for any other field in a table. 
 
1. Create a new temporary database
2. Import the backup
 
3. Then I had to run a SQL command to copy over the old correct values from the temporary database.
Here's what I run to restore the correct "create_time" values:
USE database1
UPDATE table1 t1 
JOIN tempdatabase1.table1 as t2 ON
   t1.id = t2.id
SET
   t1.create_time = t2.create_time

Note: The following name aliases were created:

database1.table1 = t1
tempdatabase1.table1 = t2


4. After restoring the damaged values, the temporary database can be removed.