Hey there, let's walk through the problems in your SQL code that are causing that syntax error, plus a couple of other gotchas you might run into:

Key Issues Identified #

• Missing semicolon on the CHECK constraint
  This is the direct cause of your ERROR: syntax error at or near "INSERT" message. Your third ALTER TABLE statement (adding the CHECK rule) doesn't end with a semicolon. PostgreSQL treats the following INSERT as part of this incomplete statement, leading to the syntax break.

• Invalid foreign key reference
  The REFERENCES clause for foreign keys needs to specify the target table and column in the format REFERENCES table_name(column_name). You wrote REFERENCES emp_id, which is incorrect—since this is a self-referencing foreign key (managers are also employees), it should be REFERENCES employee(emp_id).

• Optional but recommended: Use PRIMARY KEY instead of just UNIQUE
  Your emp_id is a unique, non-null identifier for employees—this is exactly what a primary key is designed for. A primary key automatically enforces uniqueness and non-nullness, making it more concise and standard than a separate UNIQUE constraint.

Corrected Full SQL Code #

-- Create the employee table
CREATE TABLE employee ( 
    emp_id     integer NOT NULL, 
    first_name varchar(15) NOT NULL, 
    last_name  varchar(15) NOT NULL, 
    manager    integer 
); 

-- Add constraints with proper syntax
ALTER TABLE employee ADD CONSTRAINT pk_emp_id PRIMARY KEY (emp_id); -- Primary key replaces standalone unique constraint
ALTER TABLE employee ADD FOREIGN KEY (manager) REFERENCES employee(emp_id);
ALTER TABLE employee ADD CHECK (emp_id != manager); -- Added missing semicolon here

-- Insert employees in the correct order (manager first!)
INSERT INTO employee (emp_id, first_name, last_name)
VALUES  (2, 'Dave', 'Smith');

INSERT INTO employee VALUES
(1, 'Tom', 'Jones', 2);

One More Important Note #

Notice we inserted Dave (the manager, emp_id 2) first before Tom. Since the manager column references emp_id, the manager's ID must exist in the table before we can assign it as a value—otherwise, you'll hit a foreign key violation error.

内容的提问来源于stack exchange，提问作者James Lindsay