Absolutely you can pull this off! There are a couple of reliable ways to make sure your Color and Color_ID values always match the specified pairs (Red=1, Blue=2, Yellow=3). Here are the most practical approaches:

1. Use a CHECK Constraint (Direct, Fixed Set) #

If your color list won’t change anytime soon, you can expand the CHECK constraint to explicitly validate each valid color-ID combination directly in the table definition:

CREATE TABLE Colors (
    Color CHAR(10) NOT NULL,
    Color_ID INT NOT NULL,
    -- Constraint to enforce valid color-ID pairs
    CONSTRAINT CK_Color_Matches_ID 
        CHECK (
            (Color = 'Red' AND Color_ID = 1)
            OR (Color = 'Blue' AND Color_ID = 2)
            OR (Color = 'Yellow' AND Color_ID = 3)
        )
)
GO

How it works: #

Every time you insert or update a row in the Colors table, the database will check that the Color and Color_ID fit one of the allowed pairs. Trying to add something like ('Red', 2) will throw an error and get rejected immediately.

2. Use a Reference Table with Foreign Key (Scalable Approach) #

If you might need to add more colors later, a reference table is the smarter choice—it’s flexible and easier to maintain long-term:

First, create a table that stores all valid color-ID mappings:

CREATE TABLE ValidColors (
    Color_ID INT NOT NULL PRIMARY KEY,
    Color CHAR(10) NOT NULL UNIQUE
)
GO

-- Populate with your initial valid pairs
INSERT INTO ValidColors (Color_ID, Color)
VALUES (1, 'Red'), (2, 'Blue'), (3, 'Yellow')
GO

Then link your main Colors table to this reference table using a composite foreign key:

CREATE TABLE Colors (
    Color CHAR(10) NOT NULL,
    Color_ID INT NOT NULL,
    -- Foreign key ensures only pre-approved color-ID pairs are used
    CONSTRAINT FK_Colors_ValidColors 
        FOREIGN KEY (Color_ID, Color) REFERENCES ValidColors (Color_ID, Color)
)
GO

Why this is better: #

• Adding a new color only requires inserting a row into ValidColors—no need to tweak constraints on the main table.
• It centralizes your valid color definitions, making it easier to keep consistency across your entire database.

Quick Dialect Note: #

Some older database versions (like MySQL 5.6 and earlier) ignore CHECK constraints by default. If you’re working with such a system, the reference table approach is the most reliable way to enforce this rule.

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