Example of MySQL foreign key constraint in relational database design

In relational database design, some of tables have several foreign keys, which point to parent tables.
When the rows in  parent tables  are deleted or updated, what is the behavior of child tables?
Are they deleted or updated accordingly, or et to NULL, or reject the parent table delete/update?
Suppose we have users table:
CREATE TABLE users
(
user_id int NOT NULL,
user_name varchar(45) NOT NULL,
PRIMARY KEY (user_id)
)

We have another table  pictures,  user_id is foreign key which is primary key in table users.
Using foreign key to prevent  insert user_id in table pictures which is invalid in table users.
We can use  naming of foreign key constraint, which can be used in drop foreign key.
CREATE TABLE pictures
( 
pic_id  int NOT NULL,
user_id int NOT NULL,
PRIMARY KEY (pic_id),
CONSTRAINT fk_pictures FOREIGN KEY (user_id) REFERENCES users(user_id)
ON DELETE CASCADE ON UPDATE CASCADE
)

 Now we can drop the FOREIGN KEY constraint using the following SQL:
ALTER TABLE pictures DROP FOREIGN KEY fk_pictures

When the primary key is updated or deleted on parent table, what is the behavior of the child table?
 We have four options:

  RESTRICT | CASCADE | SET NULL | NO ACTION

CASCADE: automatically delete or update the child table when the parent table is deleted or rejected
SET NULL: the foreign key table is set NULL  child table when the parent table is deleted or rejected
RESTRICT:  Rejects the delete or update operation for the parent table
NO ACTION: the same as RESTRICT

 INSERT INTO `users`  VALUES
('1', 'jiansen'), ('2', 'andy')
INSERT INTO `pictures`  VALUES('1', '1')
will be OK
INSERT INTO `pictures`  VALUES('2', '3')
will fail due to that   user_id=3 is not in users table.
Reference:
http://www.w3schools.com/sql/sql_foreignkey.asp
http://dev.mysql.com/doc/refman/5.5/en/create-table-foreign-keys.html

Video:  MySQL foreign key constraint

Run Linux command asking input or password in background

When we run a Linux command in background such as
scp test.sql jiansen@example.com:/home/jiansen &
or  ignore the hangup signal
nohup scp test.sql jiansen@example.com:/home/jiansen &
We can not input  password.
To make an input for  background job, First we use
scp test.sql jiansen@example.com:/home/jiansen
input password, then type Ctrl +Z to suspend the job
then type
bg
to put it in background.
To list all process
ps aux
To list process containing scp and find process id
ps aux |grep scp
To kill a process
kill processid
To force to kill, this is strong:
kill -9 processid

Install PHP ibm_db2 extension in Linux (redHat)

Install PHP ibm_db2 extension in Linux (redHat) to make sb2_connect function worinkg in Linux:
1) check Linux version

uname -a

or
 uname -m

return
x86_64
2) Go to ibm db2 download website to Download Data Server Driver Package

 https://www-304.ibm.com/support/docview.wss?uid=swg27016878

fix pack: DSClients-linuxx64-dsdriver-10.5.0.3-FP003

IBM Data Server Driver Package (Linux/x86-64 64 bit) V10.5 Fix Pack 3

This is for PHP5.3

3) sudo mkdir /opt/ibm

cd /opt/ibm

download v10.5fp3_linuxx64_dsdriver.tar.gz

and upload to /opt/ibm
4) sudo tar -xvf v10.5fp3_linuxx64_dsdriver.tar.gz
cd /opt/ibm/dsddriver
sudo chmod 755 installDSDriver
5) Install ksh
sudo yum install ksh

6) Install ibm_db2 driver
sudo ksh installDSDriver
7) To make  phpize working which is needed for sudo pecl install ibm_db2
sudo yum install php-devel
8) sudo pecl install ibm_db2
export IBM_DB_HOME=/opt/ibm/dsdriver
sudo pecl install ibm_db2

9) in db2 install directory message, enter
/opt/ibm/dsdriver
10) return result
Build process completed successfully
Installing '/usr/lib64/php/modules/ibm_db2.so'
install ok: channel://pecl.php.net/ibm_db2-1.9.5
configuration option "php_ini" is not set to php.ini location
You should add "extension=ibm_db2.so" to php.ini
/usr/lib64/php/modules/ibm_db2.so
11)  locate php.ini
/etc/php.ini
12)
 sudo vi  /etc/php.ini
add
extension=/usr/lib64/php/modules/ibm_db2.so
13) Restart Apache
 sudo service httpd restart

Reference:
http://www.php.net/manual/en/ibm-db2.installation.php

PHP connect IBM db2 database in XAMPP

php_ibm_db2.dll is needed for PHP db2_connect function to connect to IBM db2 database.
1) Go to (for Windows)
http://sourceforge.net/projects/db2mc/files/IBM_DB2%20PHP%20Driver%20for%20Windows/
2) Click PHP 5.4._
Latest xampp is PHP5.5, but latest php_ibm_db2.dll is for PHP5.4
I have to install two version of XAMPP, one is PHP5.5, another PHP5.4 for php_ibm_db2.dll
3) Download
php_ibm_db2-1.9.5-5.4-ts-vc9-x86.zip
and unzip
4) Copy php_ibm_db2.dll to php/ext/ directory
5) In php.ini, add
extension=php_ibm_db2.dll
6)Restart Apache in XAMPP
7) My PHP test code to connect to IBM db2 database, replace to your database name, user name and password
<?php
//db2 express c  (v10.5) in local
$database = "SAMPLE";
$user = "db2admin";
$password = "admin123";

$conn = db2_connect($database, $user, $password);

if ($conn) {
echo "Connection succeeded.";
db2_close($conn);
}
else {
echo "Connection failed.";
}
?>
For Linux:
reference:
http://www.php.net/manual/en/ibm-db2.installation.php
Video: PHP connect IBM db2 database in XAMPP

Linux, automatically backup MySQL database daily

Backup MySQL database daily using cron job
1) create a bk directory under home directory, in my case /home/jiansen,
cd bk
2) Create a shell scrip MySQLdump.sh
mysqldump  -u root -pmypass mydb | gzip >MySQLDB_`date +"%Y%m%d"`.sql.gz
replace root,  mypass, mydb to your databse username, password and database name
date +"%Y%m%d
is shell comand to produce year, month and day, for example  20140603
example output
 MySQLDB_20140603.sql.gz
3) Create a cron job
 crontab -e
30 23 * * * /home/jiansen/bk/MySQLdump.sh 2>&1>>/home/jiansen/bk/mysqbackup.log
each night  23:30pm run  /home/jiansen/bk/MySQLdump.sh and dump errors
to logfile 
4) to list cron job
  crontab -l
5) To remove cron job
  crontab -r
Video: Linux, automatically backup MySQL database daily