MorkaLork.com

Adding a foreign key to a table

Written: 2009-10-04 07:30:17
Mood: Happy
Subject: programming

It turned out that adding a foreign key is not so hard in SQL. But I wanted more than that, I wanted to make sure that if an item was deleted having a foreign key that all items connected to it were deleted as well, this is the command for adding a foreign key that deletes connected members:

CREATE TABLE `Person` (

  `Name` varchar(150) NOT NULL,

  `Age` int(3) NOT NULL,

  `Gender` varchar(25) NOT NULL,

  `id` int(11) NOT NULL auto_increment,

  PRIMARY KEY  (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

CREATE TABLE `members` (

  `id` int(11) NOT NULL auto_increment,

  `username` varchar(255) NOT NULL,

  `password` varchar(255) NOT NULL,

  `person_id` int(11) NOT NULL,

  PRIMARY KEY  (`id`),

  KEY `FK_person_id` (`person_id`)

) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

ALTER TABLE members ADD CONSTRAINT FK_person_id FOREIGN KEY ( person_id ) REFERENCES Person( id ) ON DELETE CASCADE