SQL value contrains to another table data

Question

I have a couple of tables, lets say I have a cars table and I have another table which hold all types of cars available(just to avoid multiple entries on the cars table) so I want a constraint on my cars table that has a "list/set" of types of cars FROM the TypesOFCar Table this table contains (Make, Model, etc..) how can I archive this.

I want it modular so I can just add another kind of car to the TypeOfCar table and it becomes available on the Cars table, thx in advance.

Answer 1

You want to add a CarType column to your Cars table and make it a foreign key to your TypeOfCar table.

Answer 2

The best way to implement this would be through a foreign key constraint. Essentially, you derive your tables such like:

CREATE TABLE dbo.CarType
(
    [Id] INT NOT NULL DEFAULT(1, 1),
    [Description] VARCHAR(255) NOT NULL

    CONSTRAINT PK_CarType PRIMARY KEY NONCLUSTERED ([Id])
)

CREATE TABLE dbo.Car
(
  [Id] INT NOT NULL DEFAULT(1, 1),
  [Registration] VARCHAR(7) NOT NULL,
  [CarType_Id] INT NOT NULL

  CONSTRAINT PK_Car PRIMARY KEY NONCLUSTERED ([Id]),
  CONSTRAINT FK_Car_CarType_Id FOREIGN KEY ([CarType_Id]) REFERENCES dbo.CarType ([Id])
)

In those example tables, I create a foreign key constraint that maps the CarType_Id column of the Car table to the Id column of the CarType. This relationship enforces that a CarType item must exist for the value being specified in the Car table.