How can I create a table with a column with 3 only possible given values?

Question

I'm making a SQL database with MySQL.

I need a column that has only 3 possible values: "Total" "Hours" "OnDemmand". I mean that user has to select one of these three values, he will can't select another value.

How can I do it?

Answer 1

See the ENUM type. http://dev.mysql.com/doc/refman/5.0/en/enum.html

i.e.

CREATE TABLE sizes (
    name ENUM('small', 'medium', 'large')
);

Answer 2

In Oracle, you can create a check constraint for the column like this -

ALTER TABLE yourtable
add CONSTRAINT yourcolumn
   CHECK (yourcolumn IN ('Total', 'Hours', 'OnDemand'));

There should be equivalent checks incase you are using other database.

Answer 3

If you want to build it into the table itself you would need to use a check constraint, like so:

CREATE TABLE YourTable
(
  YourColumn VARCHAR(50) NOT NULL,
  CONSTRAINT ck_YourConstraint CHECK (YourColumn = 'Total' OR YourColumn = 'Hours' OR YourColumn = 'OnDemand')
)

However, you may want to look into normalizing your database design a step further and putting those into a separate lookup table that your application uses. This will allow you great flexibility in the future in the event that those lookups are modified in any way.