Search code examples
pythonmysqlmysql-python

Python MySQL: DatabaseError: 1364 (HY000): Field 'emp_no' doesn't have a default value

I've installed MySQL on my Windows desktop. The installation was successful. I downloaded the employees data from github and added it to my MySql server. I confirmed that through the MySQL 8.4 Command Line Client

I'm working through the Documentation for the MySQL Connector/Python Developer Guide. I got to inserting data into the employee database. I get this error: DatabaseError: 1364 (HY000): Field 'emp_no' doesn't have a default value

The answers to this question are about PHP, not python: mysql error 1364 Field doesn't have a default values

This question is about default values for Datetime: How do you set a default value for a MySQL Datetime column?

Except for the **config, this code is a cut-n-paste from the dev.mysql.com hyperlink. And except for the 'password': '****', here's the code that I used:

from __future__ import print_function
from datetime import date, datetime, timedelta
import mysql.connector

config = {
    'user': 'root',
    'password': '****',
    'host': '127.0.0.1',
    'database': 'employees'
}

cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()

tomorrow = datetime.now().date() + timedelta(days=1)

add_employee = ("INSERT INTO employees "
               "(first_name, last_name, hire_date, gender, birth_date) "
               "VALUES (%s, %s, %s, %s, %s)")
add_salary = ("INSERT INTO salaries "
              "(emp_no, salary, from_date, to_date) "
              "VALUES (%(emp_no)s, %(salary)s, %(from_date)s, %(to_date)s)")

data_employee = ('Geert', 'Vanderkelen', tomorrow, 'M', date(1977, 6, 14))

# # Insert new employee
# cursor.execute(add_employee, data_employee)
# emp_no = cursor.lastrowid

# # Insert salary information
# data_salary = {
#   'emp_no': emp_no,
#   'salary': 50000,
#   'from_date': tomorrow,
#   'to_date': date(9999, 1, 1),
# }
# cursor.execute(add_salary, data_salary)

# # Make sure data is committed to the database
# cnx.commit()

cursor.close()
cnx.close()

Now, I know it connects because if I comment out the two Insert blocks, it runs fine. But here is the error message when it runs as posted in this question.

Traceback (most recent call last):
  File "C:\Users\User\Documents\Python\Tutorials\.venv\Lib\site-packages\mysql\connector\connection_cext.py", line 697, in cmd_query
    self._cmysql.query(
_mysql_connector.MySQLInterfaceError: Field 'emp_no' doesn't have a default value

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "c:\Users\User\Documents\Python\Tutorials\Add_to_Employees.py", line 27, in <module>
    cursor.execute(add_employee, data_employee)
  File "C:\Users\User\Documents\Python\Tutorials\.venv\Lib\site-packages\mysql\connector\cursor_cext.py", line 372, in execute
    result = self._cnx.cmd_query(
             ^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\User\Documents\Python\Tutorials\.venv\Lib\site-packages\mysql\connector\opentelemetry\context_propagation.py", line 102, in wrapper
    return method(cnx, *args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\User\Documents\Python\Tutorials\.venv\Lib\site-packages\mysql\connector\connection_cext.py", line 705, in cmd_query
    raise get_mysql_exception(
mysql.connector.errors.DatabaseError: 1364 (HY000): Field 'emp_no' doesn't have a default value

DISCLAIMER: I know not to put my password in the file. I trying to solve one problem at a time.

EDIT:
When I ALTER TABLE employees MODIFY emp_no INT NOT NULL AUTO_INCREMENT; in MySQL 8.4 Command Line Client,I get this error:

ERROR 1833 (HY000): Cannot change column 'emp_no': used in a foreign key constraint 'dept_manager_ibfk_1' of table 'employees.dept_manager'

Here's the TABLE DEFINITON enter image description here

enter image description here

Solution 
  • ERROR 1833 (HY000): Cannot change column 'emp_no': used in a foreign key constraint 'dept_manager_ibfk_1' of table 'employees.dept_manager'

    You are getting this error because emp_no is already a foreign key in the following tables:

    • dept_emp
    • salaries
    • dept_manager
    • titles

    You need to temporarily drop these foreign key constraints, modify the column, and then re-add the constraints.

    1. Drop foreignt key constraints.

       ALTER TABLE dept_emp DROP FOREIGN KEY dept_emp;
 ALTER TABLE dept_manager DROP FOREIGN KEY dept_manager;
 ALTER TABLE salaries DROP FOREIGN KEY salaries;
 ALTER TABLE titles DROP FOREIGN KEY titles;

    2. Modify the emp_no column.

       ALTER TABLE employees MODIFY emp_no INT NOT NULL AUTO_INCREMENT;

    3. Re-add the dropped foreign keys to tables.

       ALTER TABLE dept_emp ADD CONSTRAINT dept_emp FOREIGN KEY (emp_no) REFERENCES employees(emp_no);
 ALTER TABLE dept_manager ADD CONSTRAINT dept_manager FOREIGN KEY (emp_no) REFERENCES employees(emp_no);
 ALTER TABLE salaries ADD CONSTRAINT salaries FOREIGN KEY (emp_no) REFERENCES employees(emp_no);
 ALTER TABLE titles ADD CONSTRAINT titles FOREIGN KEY (emp_no) REFERENCES employees(emp_no);

    Maybe this will help https://kedar.nitty-witty.com/blog/mysql-change-datatype-column-foreign-key-constraint