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;