How to add constraint to a colum in MYSQL only to hold values which are already present in another column of same table?

Question

This is the table that I am supposed to create:

Table

In Manager_ID column I am supposed to add a constraint but I am not sure how to do It

This is my code:

CREATE TABLE CANDIDATE
(
    Candidate_ID FLOAT(6) PRIMARY KEY,
    Candidate_Name VARCHAR(20) NOT NULL,
    Candidate_Email VARCHAR(30) UNIQUE,
    Candidate_Dept CHAR(2) DEFAULT 'HR',
    Manager_ID VARCHAR(30),
    CONSTRAINT CHECK (Candidate_Email LIKE '%@%.%'),
    CONSTRAINT CHECK (Manager_ID IN (SELECT DISTINCT Candidate_ID FROM CANDIDATE))
);

Can someone help me to add necessary statment to achive above given task.

Answer 1

Not a check, but foreign key constraint.

I don't know MySQL syntax; in Oracle, that would be

constraint fk_mgr_cand foreign key (manager_id) references candidate (candidate_id)

I hope you can apply something similar to MySQL.

Answer 2

You are confusing CHECK constraints with FOREIGN KEY constraints. Your second constraint should be a foreign key:

CREATE TABLE CANDIDATE (
    Candidate_ID FLOAT(6) PRIMARY KEY,
    Candidate_Name VARCHAR(20) NOT NULL,
    Candidate_Email VARCHAR(30) UNIQUE,
    Candidate_Dept FLOAT(2) DEFAULT 'HR',
    Manager_ID VARCHAR(30),
    CONSTRAINT CHECK (Candidate_Email LIKE '%@%.%'),
    CONSTRAINT fk_candidate_manager_id FOREIGN KEY (Manager_ID) REFERENCES CANDIDATE(Candidate_ID)
);

I don't understand this definition:

    Candidate_Dept FLOAT(2) DEFAULT 'HR',

'HR' is not a valid floating point value. Presumably, you intend a string for that column. And you should never use floating point values for primary keys!

Here is an actually working create table statement that is reasonable:

CREATE TABLE CANDIDATE (
    Candidate_ID INT PRIMARY KEY,
    Candidate_Name VARCHAR(20) NOT NULL,
    Candidate_Email VARCHAR(30) UNIQUE,
    Candidate_Dept VARCHAR(20) DEFAULT 'HR',
    Manager_ID INT,
    CONSTRAINT CHECK (Candidate_Email LIKE '%@%.%'),
    CONSTRAINT fk_candidate_manager_id FOREIGN KEY (Manager_ID) REFERENCES CANDIDATE(Candidate_ID)
);