Assistance Solving SQL Primary Key Constraint Problem-CodePudding

I tried to create an Employee table but seems to get a constraint error..

CREATE TABLE EMPLOYEE(

    EmpID            int(4),
    FName            varchar(20),
    LName            varchar(20),
    EmpSex           char(2)      CHECK( EmpSex IN (‘M’, ‘F’) ),
    EmpDoB           date,
    M_S              char(1)      CHECK( M_S IN (‘S’, ‘M’, ‘W’, ‘D’ ) ),
    EmpPhoneNo       integer(11),
    ManagerID        int(4),

    PRIMARY KEY ( EmpID ),
    FOREIGN KEY ( ManagerID ) REFERENCES EMPLOYEE( EmpID )
);

Error Code: 3813. Column check constraint 'employee_chk_1' references other column. 0.000 sec

CodePudding user response：

String must be in single quotes (while MySQL allows also double quotes)

‘ is iterpreted as column name delimiter

Here is a good thread to the thematic When to use single quotes, double quotes, and backticks in MySQL

CREATE TABLE EMPLOYEE 
    (EmpID          Int,
    FName           Varchar(20),
    LName           Varchar(20),
    EmpSex          Char(1)
                Check(EmpSex In (
                'M', 'F')),
    EmpDoB          Date,
    M_S         Char(1)
                Check( M_S In ('S', 'M', 'W', 'D' ) ),
    EmpPhoneNo      BIGINT,
    ManagerID       Int(4),
    PRIMARY KEY (EmpID),
    FOREIGN KEY(ManagerID) REFERENCES EMPLOYEE(EmpID)
    );