MySQL gender table field

Question

If I want to create a gender field in my table, how do I make sure that my database doesn't accept any value apart from "M" or "F" ?

 $sqlCommand = "CREATE TABLE members (
             id int(11) NOT NULL auto_increment,
             ... 
             ...
             ...
             ...
             gender
             )";

Thank you

use ENUM type (http://dev.mysql.com/doc/refman/5.0/en/enum.html) — Aziz, Mar 02 '12 at 13:27
[See the ENUM type](http://dev.mysql.com/doc/refman/5.0/en/enum.html) — Michael Berkowski, Mar 02 '12 at 13:27

score 11 · Accepted Answer · edited May 23 '17 at 12:09

11

No triggers, no enums or other deamonic activities.

You can use a FOREIGN KEY to a reference table with just 2 rows:

CREATE TABLE Gender_Ref 
(   gender CHAR(1) NOT NULL,
    PRIMARY KEY (gender)
) ENGINE = InnoDB ;

INSERT INTO Gender_Ref (gender)
    VALUES
        ('F'), ('M')  ;

CREATE TABLE members 
(   id int(11) NOT NULL auto_increment,
     ... 
     ...
    gender CHAR(1) NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY gender
        REFERENCES Gender_Ref (gender)
) ENGINE = InnoDB ;

It's also good advice to "lock" the reference table so the applications code has only read access. (That's usually good for most reference tables, and if you have an Admin application, you can of course give it write access as well to the reference tables).

edited May 23 '17 at 12:09

Community

1
1

answered Mar 02 '12 at 13:31

ypercubeᵀᴹ

113,259
19
174
235

Much as I dislike the (mis-)use of ENUMs, "gender" is a case where ENUMs could reasonably considered. Personally I feel it makes the db/code/etc much easier to follow and much more intuitive that the lookup table solution you propose. – liquorvicar Mar 02 '12 at 13:57
I would consider Enums, too, for such a simple case. And dismiss them :) – ypercubeᵀᴹ Mar 02 '12 at 14:27
Ah, but if you want to add more genders later, this is the easier option since it doesn't require a schema change. :) – Marcus Adams Mar 02 '12 at 14:36

score 5 · Answer 2 · answered Mar 02 '12 at 13:37

Like pointed out in the comments, you can use ENUM like so:

gender ENUM('F','M') NOT NULL

However, you have to be careful as this will still accept the empty string too (although you'll get a warning for that):

mysql> create table t (g enum('M','F') not null);
Query OK, 0 rows affected (0.12 sec)

mysql> insert into t values ('M');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values ('');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1265 | Data truncated for column 'g' at row 1 |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

mysql> select * from t;
+---+
| g |
+---+
| M |
|   |
+---+
2 rows in set (0.00 sec)

To ensure this does not happen, you could consider setting the sql_mode (http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html) to a more restrictive value:

mysql> set sql_mode = strict_all_tables;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values ('');
ERROR 1265 (01000): Data truncated for column 'g' at row 1

However, you should investigate if this is a suitable option for you. Many existing applications (wordpress etc) don't like messing with the sql_mode so if your code is a plugin to those systems you want to avoid setting it.

You can choose to set the sql_mode server wide or session wide; The first option would be more robust, but requires configuring MySQL in a non default way, and is likely to affect other applications. Setting at the session level immediately after you open the connection should work just fine, but will clutter your application code. Pick your poison.

ypercube's suggestion to use a foreign key is also good, and is more portable to other RDBMSes than ENUM. However, you'll have to ensure your tables are both managed by the InnoDB engine. This is becoming more and more the standard so it's not a bad choice.

(if you're really paranoid, you should really ensure that the application only has read access to the gender reference table)

Heh :) Frankly, i should add that a similar thing applies to the ENUM solution: application user should not be allowed to change the definition of the table (although usually applications tend to not have knobs to make random table definition changes, whereas its completely feasible that they do have a facility for generic table independent data access) — Roland Bouman, Mar 02 '12 at 23:40

score 1 · Answer 3 · answered Mar 02 '12 at 13:28

1

You could use enum type enum('M','F').

answered Mar 02 '12 at 13:28

Shakti Singh

84,385
21
134
153

score 0 · Answer 4 · answered Mar 02 '12 at 13:29

0

You can use a trigger to check if it is coreect or you can use an enum type enum('M','F')

answered Mar 02 '12 at 13:29

Kayser

6,544
19
53
86

I would strongly advise against using a trigger, for several reasons. Please tell me, what would you do inside the trigger once you detect an invalid value? How would you prevent the update or insert from taking place? There is a proper solution for that in MySQL 5.5, but not in earlier versions. – Roland Bouman Mar 02 '12 at 13:42

MySQL gender table field

4 Answers4