Putting constraint on an attribute gender so that it only accepts 'F' or 'M' cha-CodePudding

I am doing an assignment which is implementing a database that has book, author, publisher and people tables in it.

CREATE TABLE IF NOT EXISTS Author (
    author_id INT,
    author_name VARCHAR(255),
    books_written VARCHAR(255),
    gender CHAR(1),
    birthday DATE,
    country VARCHAR(100),
    PRIMARY KEY(author_name)
);

I have to put constraint on the gender attribute of my Author table so that the only characters in a component for this attribute can be 'F' or 'M'. I only thought of CHAR(1) and i know that it is not the solution. How can i do it?

CodePudding user response：

You can use the ENUM type for the gender column:

CREATE TABLE IF NOT EXISTS Author (
author_id INT,
author_name VARCHAR(255),
books_written VARCHAR(255),
gender ENUM('F', 'M'),
birthday DATE,
country VARCHAR(100),
PRIMARY KEY(author_name)
);

CodePudding user response：

You can do:

CREATE TABLE Author (
  author_id INT,
  author_name VARCHAR(255),
  books_written VARCHAR(255),
  gender char(1) not null,
  birthday DATE,
  country VARCHAR(100),
  PRIMARY KEY(author_name),
  constraint chk1 check (gender = 'F' or gender = 'M')
);

Make sure the column is marked as NOT NULL as well to make sure the its value is either F or M.