CODEWITHSUNDEEP
mysqlsqlsqldatatypes
Shafizadeh
Shafizadeh

Reputation: 10350

What is the best data type that only accepts a few specific values?

I have some values like these:

I want to know is that possible to define some specific values for a column as valid values? (everything else would be invalid)

I know, I can implement a tagging-system the other way. Creating a separated table which contains valid tags and checks everything for validating.

But actually that tagging-system is just as an example. All I'm trying to do is defining some constant values for a column.

Upvotes: 0

Views: 128

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270301

A typical way of doing this uses a foreign key relationship.

You can define a table of values like this:

create table skills (
    skillId int auto_increment primary key,
    skill varchar(255) unique
);

insert into table_of_values (value)
    select 'PHP' union all
    select 'CSS' union all
    . . . ;

Then you can validate using a foreign key. Say:

create table PeopleSkills (
    PeopleSkillsId int auto_increment primary key,
    PeopleId int,
    Skill varchar(255),
    constraint fk_PeopleSkills_PeopleId foreign key (PeopleId) references People(PeopleId),
    constraint fk_PeopleSkills_Skills foreign key (Skill) references skills(skill)
);

Note: This example uses strings for the foreign key reference. I don't actually advocate that approach -- but that is the direct answer to your question. In practice, the constraint can use the id rather than the name:

create table PeopleSkills (
    PeopleSkillsId int auto_increment primary key,
    PeopleId int,
    SkillId int, 
    constraint fk_PeopleSkills_PeopleId foreign key (PeopleId) references People(PeopleId),
    constraint fk_PeopleSkills_SkillId foreign key (SkillId) references Skills(skillId)
);

Upvotes: 2

Mono
Mono

Reputation: 742

You could use an ENUM Type and define your constants when creating the table:

http://dev.mysql.com/doc/refman/5.7/en/enum.html

I would only use it if it is a fixed list of some values. Like "male", "female".

Upvotes: 2

Related Questions