SQL Queries

Create A Table with Constrains

CREATE TABLE IF NOT EXISTS `publisher_media_types` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `publisher_id` int(11) NOT NULL,
  `media_type_id` int(11) NOT NULL,
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  `created_by` int(11) NOT NULL,
  `modified_by` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_publisher_media_types_publisher_id` (`publisher_id`),
  KEY `fk_publisher_media_type_id` (`media_type_id`),
  KEY `fk_publisher_media_types_created_by` (`created_by`),
  KEY `fk_publisher_media_types_modified_by` (`modified_by`),
  CONSTRAINT `fk_publisher_media_types_publisher_id` FOREIGN KEY (`publisher_id`)
  REFERENCES `publishers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_publisher_media_type_id` FOREIGN KEY (`media_type_id`)
  REFERENCES `media_types` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_publisher_media_types_created_by` FOREIGN KEY (`created_by`)
  REFERENCES `logins` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_publisher_media_types_modified_by` FOREIGN KEY (`modified_by`)
  REFERENCES `logins` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;


To Remove the Constrain

ALTER TABLE `publisher_media_types` DROP FOREIGN KEY `fk_publisher_media_type_id`;
ALTER TABLE `publisher_media_types` DROP INDEX `fk_publisher_media_type_id`;


To Add the Index

ALTER TABLE `publisher_media_types` ADD KEY `fk_publisher_media_type_id` (`media_type_id`);
ALTER TABLE `publisher_media_types` ADD
CONSTRAINT `fk_publisher_media_type_id` FOREIGN KEY (`media_type_id`)
REFERENCES `media_types` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

How to fix “MySQL server has gone away” (error 2006)

Two most common reasons (and fixes) for the MySQL server has gone away (error 2006) are:
1. Server timed out and closed the connection. How to fix: check that wait_timeout variable in your mysqld’s my.cnf configuration file is large enough. Set wait_timeout = 600 seconds (you can tweak/decrease this value when error 2006 is gone), then restart the mysql.
2. Server dropped an incorrect or too large packet. If mysqld gets a packet that is too large or incorrect, it assumes that something has gone wrong with the client and closes the connection. You can increase the maximal packet size limit by increasing the value of max_allowed_packet in my.cnf file. Set max_allowed_packet = 64M (you can tweak/decrease this value when error 2006 is gone), then restart the mysql.