Foreign Key Constraints

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