MySQL enum equvalent alternate in oracle

June 5, 2010 in Oracle, SQL


One of Mysqls great features in enum data type. An ENUM is a string object with a value chosen from a list of allowed values that are enumerated explicitly in the column specification at table creation time.

But in oracle there is no such data type. So if you want to restrict column value input like mysql enum datatype in oracle you can make use of an check constraint.

-- Student Table
CREATE TABLE student
(
    reg number(10) NOT NULL,
    name varchar2(50) NOT NULL,
    dept varchar2(5) NOT NULL,
    ses number(4) NOT NULL,
    fname varchar2(50),
    mname varchar2(50),
    bday date,
    gender varchar2(6),
    cgpa number(4,2),
    grade_letter varchar2(2),
    constraint student_pk_1 PRIMARY KEY(reg), constraint student_en_1 CHECK(gender IN('Male', 'Female')),
    constraint student_fk_1 FOREIGN KEY(dept) REFERENCES dept(code) ON DELETE cascade,
    constraint student_fk_2 FOREIGN KEY(ses) REFERENCES ses(ses) ON DELETE cascade
);

Here in line 14 check function is working as like enum data type in mysql

Related posts:

To make money we lose our health, and then to restore our health we lose our money.... We live as if we are never going to die, and we die as if we never lived!

Leave a Reply