0

The problem

I'm currently trying to insert a new employee to the employees table of the test database with the same name. If I try to add a new employee specifying the emp_no it's done with success:

insert into employees.employees(
    emp_no, 
    birth_date, 
    first_name, 
    last_name, 
    gender, 
    hire_date
) 
values(
    500500, 
    date('1970-05-12'), 
    'John', 
    'Smith', 
    'M', 
    date('1991-10-08')
);

But when I try removing the emp_no, it won't be incremented by the database. I'm looking for a way of making the database increment the emp_no itself but Im' not able to find it by myself.

The database

Here's the link to the title database itself for the reference.

What I tried so far

I tried with a native query as follows:

insert into employees.employees(
    birth_date, 
    first_name, 
    last_name, 
    gender, 
    hire_date
) 
values(
    date('1970-05-12'), 
    'John', 
    'Smith', 
    'M', 
    date('1991-10-08')
);

But I'm getting the following error:

Error Code: 1364. Field 'emp_no' doesn't have a default value

So according to the solutions here, here or here I tried to enable auto incrementing the emp_no with

ALTER TABLE employees.employees MODIFY emp_no int NOT NULL AUTO_INCREMENT;

But this gave me another error

Error Code: 1833. Cannot change column 'emp_no': used in a foreign key constraint 'dept_emp_ibfk_1' of table 'employees.dept_emp'

I don't even know if that's what causes the problem and whether I should still go this way or maybe not. Maybe the problem itself lies somewhere else? I'll be very grateful for any help.

Shadow
  • 33,525
  • 10
  • 51
  • 64
big_OS
  • 381
  • 7
  • 20

1 Answers1

1

From the Github link here is the DDL for the empoyees table with AUTO_INCREMENT property added to the emp_no column:

CREATE TABLE employees (
    emp_no      INT             NOT NULL AUTO_INCREMENT,
    birth_date  DATE            NOT NULL,
    first_name  VARCHAR(14)     NOT NULL,
    last_name   VARCHAR(16)     NOT NULL,
    gender      ENUM ('M','F')  NOT NULL,    
    hire_date   DATE            NOT NULL,
    PRIMARY KEY (emp_no)
);

The original code posted should work now (without the emp_no). the double quotes around the dates and strings are still needed.

insert into employees.employees(
    birth_date, 
    first_name, 
    last_name, 
    gender, 
    hire_date
) 
values(
    date('1970-05-12'), 
    'John', 
    'Smith', 
    'M', 
    date('1991-10-08')
);
SteveC
  • 5,955
  • 2
  • 11
  • 24
  • I just realized I asked the question the wrong way (edit incoming) - I'm looking for a way of making the database increment the `emp_no` itself but Im' not able to find it by myself. If you have any idea of the solution, please share :) – big_OS Sep 06 '20 at 16:46
  • I updated to add the Identity property to the primary key and still fix the quotes :) – SteveC Sep 06 '20 at 16:52
  • I'll try it and let you know in a few minutes – big_OS Sep 06 '20 at 16:54
  • ERROR 1064 (42000) at line 41: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTITY(1,1) NOT NULL, birth_date DATE NOT NULL, first_name' at line 2 – big_OS Sep 06 '20 at 17:01
  • the version of mysql is 5.7.31 – big_OS Sep 06 '20 at 17:04
  • I added auto increment to the DDL – SteveC Sep 06 '20 at 17:05