28 posts tagged with "SQL"

When you need to know exactly what a MySQL table looks like, which columns, which types, what can be empty and which keys exist, three statements answer at different levels of detail. DESCRIBE gives a quick table of columns, SHOW CREATE TABLE gives the complete definition with every index and constraint, and INFORMATION_SCHEMA.COLUMNS lets you query the answer. This guide runs all of them on a real MySQL 9.7 table created by EF Core.

The first thing to check in a MySQL database is which tables it has. SHOW TABLES answers that in one line, and INFORMATION_SCHEMA.TABLES adds the details: storage engine, collation, size and an approximate row count. This guide runs both against a real MySQL 9.7 database and explains the one number in there you shouldn't trust.

A migration that changed an enum column from numbers to names applied fine, but undoing it failed with "Incorrect integer value: 'Adjustment' for column 'Type' at row 3". EF Core generates a Down method that changes the column back to int, and MySQL can't turn the word Adjustment into a number. The fix is to make Down translate the names back to numbers before it changes the column.

I added a two-column index to a table, and EF Core's migration failed on MySQL with "Cannot drop index 'IX_stock_movements_ProductId': needed in a foreign key constraint". The new index starts with the foreign key column, so EF Core decided the old single-column index was no longer needed and dropped it first. MySQL needs an index behind every foreign key at every moment. Swapping two calls in the migration, in both directions, fixes it.

I changed a C# property from string? to a required string, and the EF Core migration for it failed on MySQL with "BLOB, TEXT, GEOMETRY or JSON column 'Description' can't have a default value". EF Core had added DEFAULT '' to fill the rows that were empty, and MySQL doesn't accept a plain default on a longtext column. The fix is a two-line edit to the migration: fill the empty rows yourself, then make the column NOT NULL without a default.

EF Core warned me that a migration "may result in the loss of data", and on MySQL that warning turned into an error: "Data too long for column 'Name' at row 2". Worse, the migration had already changed one column before it failed. This post shows how to find the values that don't fit, see exactly what the failed migration did, fix the data, and apply the migration again.

ERROR 1146 (42S02): Table '…' doesn't exist usually means exactly that. But sometimes the table is right there, just with different capital letters, and MySQL on Linux treats Products and products as two different names. This post shows the failing query, the setting that causes it, and how to choose table names that work the same on Linux, Windows and macOS.

ERROR 1264 (22003): Out of range value for column means the number you're saving doesn't fit the column's type. For a DECIMAL column, the limit isn't the number of decimals, which MySQL quietly rounds, but the number of digits before the decimal point. This post finds a column's exact limit, shows the difference between a value that's rounded and one that's refused, and fixes it from both sides: the column and the input.

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails means you inserted or updated a row that points at a parent which doesn't exist. I got it from my own mistake: I guessed that a new category had Id 3, because it was the third one I'd created. It wasn't. This post shows why the guess was wrong, how to find the right Id, how to insert without guessing, and how to answer 400 Bad Request from a .NET C# API.

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails means the row you're deleting is still in use: another table points at it through a foreign key, and that foreign key forbids the delete. The message even tells you which table and column. This post reads it piece by piece, finds the rows that block the delete, shows two ways to clear them, and turns the error into a 409 Conflict in a .NET C# API.