Problem with inserting data to 'datacharmer/test_db' database

Question

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.

The easiest solution will be to add, in your INSERT-statement, a value for emp_no. — Luuk
– Luuk, Commented Sep 6, 2020 at 16:44
I realized that I asked the question the wrong way - the update of it is live now. — big_OS
– big_OS, Commented Sep 6, 2020 at 16:51

SteveC · Accepted Answer · 2020-09-06 17:05:07Z

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

edited Sep 6, 2020 at 17:05

answered Sep 6, 2020 at 16:39

SteveC

6,0252 gold badges14 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

big_OS Over a year ago

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

SteveC Over a year ago

I updated to add the Identity property to the primary key and still fix the quotes :)

big_OS Over a year ago

I'll try it and let you know in a few minutes

big_OS Over a year ago

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 Over a year ago

the version of mysql is 5.7.31

|

Collectives™ on Stack Overflow

Problem with inserting data to 'datacharmer/test_db' database

The problem

The database

What I tried so far

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

The problem

The database

What I tried so far

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related