Assistance Solving SQL Primary Key Constraint Problem

Question

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

First, tag the correct database engine, MySQL is totally different to SQL Server and each will have a different answer. — Dale K, Nov 17 '22 at 23:42
`Check(EmpSex In (‘M’, ‘F’)),` - such a very progressive company to work at... — Dai, Nov 17 '22 at 23:45
`EmpPhoneNo Integer(11),` <-- **This is incorrect**: _Phone numbers are not integer numbers_. e.g. They can have leading zeroes and significant special characters. — Dai, Nov 17 '22 at 23:46
All of your columns are implicitly `NULL`able, which is something to avoid in table-design. You need to add explicit `NOT NULL` modifiers to your columns as-appropriate. — Dai, Nov 17 '22 at 23:46
`Int(4)` <-- Remove the `(4)` part [it doesn't do anything](https://dev.mysql.com/doc/refman/8.0/en/numeric-type-attributes.html) and makes your statement harder to read. — Dai, Nov 17 '22 at 23:47
Your posted statement has _pretentious punctuation_: '`‘`' and '`’`' (I guess you typed this up on an iPad or macOS box?) - MySQL doesn't like that: you need to change it to ASCII single-quotes: `'`. — Dai, Nov 17 '22 at 23:49
Actually, I think my comment about (about punctuation) is the cause of the error message: MySQL is interprereting the quotes as a reference to a not-yet-defined column name. — Dai, Nov 17 '22 at 23:54

score 1 · Accepted Answer · edited Nov 18 '22 at 00:10

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)
    );

Assistance Solving SQL Primary Key Constraint Problem

1 Answers1