0

I am working with an old SQL database and have many errors when trying to insert a new data record via PDO:

Field 'field_name' doesn't have a default value

As I can see, the old PHP code in this project uses old mysql_ functions and ignores such circumstances.

The database is old and large, and I don't know if I can change the column settings in the tables without unexpected consequences. So I want add values for all these columns in my însert-request.

How can I get the list of all the SQL fields in the particular table that have no default value?

PS. I have added my SQL request as an answer, but are there possibly other options or other important considerations for my situation?

Gleb Kemarsky
  • 10,160
  • 7
  • 43
  • 68
  • 1
    Maybe this thread would be useful, https://stackoverflow.com/questions/39504212/set-default-value-to-all-columns-in-a-database – user3783243 Nov 25 '20 at 12:27

1 Answers1

0

For MariaDB this SQL request works well:

SELECT i.COLUMN_NAME, i.COLUMN_TYPE, i.COLUMN_DEFAULT, i.IS_NULLABLE
FROM information_schema.columns i
WHERE
    i.TABLE_SCHEMA = 'my_database_name'
    AND i.TABLE_NAME = 'my_table_name'
    AND i.COLUMN_DEFAULT IS NULL
Gleb Kemarsky
  • 10,160
  • 7
  • 43
  • 68