Foreign Key Constraints

From Hashmysql
Jump to: navigation, search

Creating the tables:

 CREATE TABLE authors (
   id INT(11) UNSIGNED AUTO_INCREMENT NOT NULL,
   name VARCHAR(64) NOT NULL,
 
   PRIMARY KEY(id)
 ) ENGINE = InnoDB;
 CREATE TABLE documents (
   id INT(11) UNSIGNED AUTO_INCREMENT NOT NULL,
   authorId INT(11) UNSIGNED NULL,
 
   PRIMARY KEY(id),
   INDEX(authorId)
 ) ENGINE = InnoDB;

Adding the constraints (setting the value to NULL):

 ALTER TABLE documents ADD CONSTRAINT fk_authors
   FOREIGN KEY documents(authorId)
   REFERENCES authors(id)
   ON UPDATE NO ACTION
   ON DELETE SET NULL;

Adding the constraints (deleting the child nodes):

 ALTER TABLE documents ADD CONSTRAINT fk_authors
   FOREIGN KEY documents(authorId)
   REFERENCES authors(id)
   ON UPDATE NO ACTION
   ON DELETE CASCADE;

Adding test data:

 INSERT INTO authors (name) VALUES ('Ted'), ('George'), ('Bob');
 INSERT INTO documents(authorId) VALUES (1), (2), (3);

Deleting a parent node:

 DELETE FROM authors WHERE id = 1;

The database contents:

 SELECT * FROM authors;
 ID	NAME
 2	George
 3	Bob
 SELECT * FROM documents;
 ID	AUTHORID
 1	(null) 
 2	2
 3	3

Deleting a child node:

 DELETE FROM documents WHERE id = 3;

The database contents:

 SELECT * FROM authors;
 ID	NAME
 1	Ted
 2	George
 3	Bob
 SELECT * FROM documents;
 ID	AUTHORID
 1	1
 2	2

Using a full outer join to see all the rows:

 SELECT documents.id, authors.name authorName FROM documents
   LEFT JOIN authors ON documents.authorId = authors.id
     WHERE authors.id IS NULL
 UNION ALL
 SELECT documents.id, authors.name FROM authors
   LEFT JOIN documents ON authors.id = documents.authorId
     WHERE documents.authorId IS NULL;
 ID	        AUTHORNAME
 (null)	Bob