CODEWITHSUNDEEP
sqlsql-server
Zevvysan
Zevvysan

Reputation: 303

SQL Can I check matching ID for attribute?

Assume I have an assortment of colors the user can put in. For now let's say we have three colors, red, blue, and yellow. Each color has a color ID, Red(1), Blue(2), and Yellow(3). I'm wondering if I can make a table that's something like this:

CREATE TABLE Colors
(
    Color CHAR(10) NOT NULL
    ,Color_ID INT NOT NULL
            CHECK (*Does Color ID match the color?*)
)
GO

I.e. if you put in Red, the ID must be "1". Is this possible? Thanks!

Upvotes: 0

Views: 51

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270351

You can validate the colors in one of two ways. One method is to use a check constraint in the table that contains them. This would not be the "colors" table, but something else. For instance:

create table blouses
    blouse_id int identity(1, 1) not null,
    purchase_date date,
    color varchar(255),
    constraint chk_blouses_color check (color in ('red', 'blue', 'yellow'))
);

In this method, the colors are stored not as numbers but strings. The constraint guarantees that the values are correct.

The second way to ensure correct values is with a foreign key constraint. This might look like:

create Colors (
    color_id int identity(1, 1) primary key
    Color varchar(10) not null unique
);

insert into colors (color)
    values ('red'), ('blue'), ('yellow');

This would then be referenced as:

create table blouses
    blouse_id int identity(1, 1) not null,
    purchase_date date,
    color_id int not null,
    constraint fk_blouses_color foreign key (color_id) references colors(color_id)
);

Upvotes: 2

MJH
MJH

Reputation: 1750

Yes, they are usually called "lookup tables", you add an ID column and a value, then you reference the ID column in your other tables.

As shawnt00 says (in the comments), you can set up referential integrity between tables using a "Foreign Key Constraint", so that the other tables can not have values for other colors than the ones you have defined in your "Colors" table.

Upvotes: 0

Related Questions