如何为MariaDB/MySQL中的数据表添加外键？（附表结构）

Alright, let's walk through how to add foreign keys to your MariaDB/MySQL tables step by step. First, a quick recap of the prerequisites because foreign keys have strict rules to function correctly:

• The column you're referencing (like channels.id) must be a primary key or unique index (which it is, since it's the primary key of channels).
• The data types of the referencing column (in categories) and the referenced column (in channels) must match exactly (same integer type, unsigned status, etc.).
• Your tables must use the InnoDB engine (which your channels table already does—good call!).

Since your categories table definition was cut off, I'll assume you want to link categories to channels (a common one-to-many relationship: one channel has multiple categories). Let's cover both scenarios: adding a foreign key when creating the table, and adding it to an existing table.

1. Add Foreign Key When Creating the categories Table #

If you're still setting up the categories table, you can define the foreign key directly in the CREATE TABLE statement:

CREATE TABLE `categories` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `channel_id` int(10) unsigned NOT NULL, -- This column links to channels.id
  `content` text DEFAULT NULL, -- Filling in the truncated part of your original definition
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  -- Define the foreign key constraint
  CONSTRAINT `fk_categories_channel` FOREIGN KEY (`channel_id`) 
    REFERENCES `channels`(`id`)
    ON DELETE RESTRICT -- Optional: Control what happens when a channel is deleted
    ON UPDATE CASCADE -- Optional: Control what happens when a channel's id is updated
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

Quick breakdown of the optional ON DELETE/ON UPDATE rules: #

• RESTRICT (default): Prevents deleting a channel if there are linked categories (stops accidental data loss).
• CASCADE: Deletes all linked categories automatically when a channel is deleted. Use this cautiously!
• SET NULL: Sets channel_id to NULL in categories if the linked channel is deleted (only works if channel_id allows NULL values).
• NO ACTION: Similar to RESTRICT, but the check happens at the end of the transaction instead of immediately.

2. Add Foreign Key to an Existing categories Table #

If categories already exists, you'll need two steps: first add the linking column, then add the foreign key constraint.

Step 1: Add the channel_id column (if it doesn't exist) #

ALTER TABLE `categories`
ADD COLUMN `channel_id` int(10) unsigned NOT NULL;

If you want to allow channel_id to be empty, replace NOT NULL with DEFAULT NULL.

Step 2: Add the foreign key constraint #

ALTER TABLE `categories`
ADD CONSTRAINT `fk_categories_channel`
FOREIGN KEY (`channel_id`)
REFERENCES `channels`(`id`)
ON DELETE RESTRICT
ON UPDATE CASCADE;

Critical Checks Before Adding the Foreign Key #

• If categories already has data, make sure every channel_id value matches an existing id in channels. If there are mismatches, the foreign key creation will fail.
• The constraint name (fk_categories_channel) must be unique within the categories table.
• Double-check that channel_id has the exact same data type as channels.id (int(10) unsigned in your case). Even a tiny mismatch (like missing unsigned) will throw an error.

内容的提问来源于stack exchange，提问作者Jenski