Usually XREF tables have only two columns with no Primary Key. That’s right, the two columns together make them unique. They are both Foreign Keys to other tables. A real life example of this would be in any standard CRM. Let’s go over this example right now.
Many CRM’s could have a table to store notes, and a table to store accounts. If you have one user managing many accounts, they may need to put the same note for all accounts they are managing. To connect these notes to the accounts we will use an xref table.
Create a schema called ‘test_db’. Now create our accounts and notes table:
CREATE TABLE `test_db`.`accounts` ( `id` INT NOT NULL AUTO_INCREMENT , `name` VARCHAR(45) NOT NULL , `phone` VARCHAR(24) NULL , `fax` VARCHAR(24) NULL , PRIMARY KEY (`id`) , UNIQUE INDEX `id_UNIQUE` (`id` ASC) ) ENGINE = InnoDB; CREATE TABLE `test_db`.`notes` ( `id` INT NOT NULL AUTO_INCREMENT , `subject` VARCHAR(45) NOT NULL , `body` VARCHAR(256) NOT NULL , PRIMARY KEY (`id`) , UNIQUE INDEX `id_UNIQUE` (`id` ASC) ) ENGINE = InnoDB;
These tables should be created first, so that the ID’s in them actually exist when we create the foreign keys in our XREF table. We are going to name the XREF table account_notes_xref. This is preference, but really good practice, as it is a single account’s note’s. The notes will be displayed on the account so the account owns the notes.
You could also get away with account_notes, but for sake of the title, we will add xref to it. This also makes things easier to spot when looking through the database. So let’s make the table:
CREATE TABLE `test_db`.`account_notes_xref` ( `account_id` INT NOT NULL , `note_id` INT NOT NULL , PRIMARY KEY (`account_id`, `note_id`) , INDEX `acx_account_id` (`account_id` ASC) , INDEX `acx_note_id` (`note_id` ASC) , CONSTRAINT `acx_account_id` FOREIGN KEY (`account_id` ) REFERENCES `test_db`.`accounts` (`id` ) ON DELETE CASCADE ON UPDATE NO ACTION, CONSTRAINT `acx_note_id` FOREIGN KEY (`note_id` ) REFERENCES `test_db`.`notes` (`id` ) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE = InnoDB;
Notice that when making the foreign key constraints, we said ‘on delete cascade’. This is important and you want to use this wisely. What will happen is if a note is in the xref table, and you delete the note, the record in the xref table will automatically be deleted as well. This could be OK for most systems, yet other systems have requirements for history information.
Let’s try this out, enter in some records into your database. (On the xref table make sure the ID’s are correct)
INSERT INTO `test_db`.`accounts` (`name`, `phone`, `fax`) VALUES ('Account1', '5042225545', '5045552245'); INSERT INTO `test_db`.`accounts` (`name`, `phone`, `fax`) VALUES ('Account2', '5042225555', '5045552225'); INSERT INTO `test_db`.`notes` (`subject`, `body`) VALUES ('Subject', 'Here is a test note.'); INSERT INTO `test_db`.`account_notes_xref` (`account_id`, `note_id`) VALUES (1, 1); INSERT INTO `test_db`.`account_notes_xref` (`account_id`, `note_id`) VALUES (1, 1);
Now go delete the note.
DELETE FROM `test_db`.`notes` WHERE `id`='2';
All of your account_notes_xref entries should be gone. For auto-incremented fields CASCADE on UPDATE is not necessary, as you will never update that unique primary-key, you will only delete it.
So there you have your XREF table. You can tie as many notes to accounts as you want and vise-verse. This saves space in your database. The double primary-key on the table will prevent you from attaching the same note to the same account twice.
You will also get an error if you try to enter an account_id or note_id that doesn’t exist due to the foreign-key constraints. Remember to check for these errors in your code.
I hope this tutorial has been useful, keep an eye out for one coming up soon when we don’t use the ON DELETE CASCADE and use triggers and stored procedures instead.