minte9
SoftwareDevelopment / MYSQL





Add foreign key

Foreign keys helps prevent admin input errors, producing a more accurate database. Using constraint any attempt to link to a record which does not already exist will fail. 
 
ALTER TABLE article_log ADD FOREIGN KEY (article_id) 
REFERENCES article (id) ON DELETE CASCADE;

Modify foreign key 

 
SHOW CREATE TABLE catg_path;

CREATE TABLE `catg_path` (
  `ancestor` int(10) unsigned NOT NULL,
  `descendant` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`ancestor`,`descendant`),
  KEY `descendant` (`descendant`),
  CONSTRAINT `catg_path_ibfk_1` FOREIGN KEY (`ancestor`) REFERENCES `catg` (`id`),
  CONSTRAINT `catg_path_ibfk_2` FOREIGN KEY (`descendant`) REFERENCES `catg` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
;

ALTER TABLE catg_path DROP FOREIGN KEY catg_path_ibfk_1;

ALTER TABLE catg_path ADD FOREIGN KEY (ancestor) REFERENCES catg (id)
ON DELETE CASCADE;






Questions and answers:
Clink on Option to Answer




1. ALTER TABLE log ________ REFERENCES article (id)

  • a) ADD FOREIGN KEY (article_id)
  • b) ADD CONSTRAINT artile_id FOREIGN KEY

2. ALTER TABLE log ______________

  • a) DELETE FOREIGN KEY article_id
  • b) DROP FOREIGN KEY log_ibfk_1

3. View article_log foreign keys

  • a) SHOW TABLE article_log
  • b) SHOW CREATE TABLE article_log

4. ADD FOREIGN KEY (article_id) ____________

  • a) REFERENCE article_id
  • b) REFERENCES article (id)

5. Delete FK when reference is deleted

  • a) ALTER TABLE .... ADD ... ON DELETE (article_id)
  • b) ALTER TABLE .... ADD ... ON DELETE CASCADE


References: