28 posts tagged with "SQL"

Part 1 connected a .NET C# Web API to MySQL, but the database is still empty. This part describes the inventory data as C# classes and lets EF Core migrations create the matching MySQL tables, with the column types, keys, indexes and constraints you'd otherwise write by hand. You'll read the SQL before it runs, apply it, change the model and add a second migration, and then check every table from inside MySQL.

dotnet ef database update applies migrations straight to a database. Often you want the SQL instead: to read exactly what will run, to have a database administrator review it, or to apply it where the tools can't reach. dotnet ef migrations script generates that SQL from your migrations. This guide covers every form of the command with real scripts for MySQL, and shows why reading the SQL catches problems the C# migration hides.

What should happen to a category's products when the category is deleted? EF Core answers for you, unless you answer first: for a required relationship, its default is to delete the products too, silently. This guide shows that default happening in MySQL, switches it to Restrict so the database refuses instead, and shows the two different errors you get afterwards, one from MySQL and one from EF Core itself.

The most common query on a history table is "this item's entries, newest first". An index on just the item's Id finds the rows, but MySQL then has to sort them. An index on the Id and the time does both jobs. This guide creates one from EF Core's model, explains why the order of its columns decides what it can speed up, fixes the migration order MySQL needs, and uses EXPLAIN to prove the index is used.

Some values must never repeat: a product's SKU, a category's name, a user's email. Checking in code isn't enough, because two requests at the same moment can both pass the check. A unique index makes the database refuse the second one. This guide adds two from EF Core's model, applies them to MySQL 9.7, and shows what MySQL treats as a duplicate, which isn't always what you'd expect.

Validation in your API stops most bad data, but not data from a script, a second app, or a bug that skips the validation. A CHECK constraint makes the database itself refuse a row that breaks a rule, such as a negative price. This guide adds three to a products table from EF Core's model, applies them with a migration, and proves MySQL enforces them on both inserts and updates.

A table with a dozen columns doesn't fit in a terminal, and the mysql client's boxed output wraps into lines nobody can read. Ending the statement with \G instead of ; prints each row vertically, one column: value per line. This guide shows the difference on a real MySQL 9.7 table, and the client option that makes every result vertical.

A CHECK constraint is a rule MySQL enforces on every row, such as "a price can't be negative". When something refuses to save with ERROR 3819, or you just want to know which rules a database already has, you need to list them. MySQL keeps them in two INFORMATION_SCHEMA tables, and neither one has everything on its own. This guide joins them, on a real MySQL 9.7 database, and shows the quicker way for a single table.

A foreign key ties a column to another table: a product's CategoryId must be the Id of a real category. Before you delete a row, rename a table or change a column, you need to know which foreign keys are involved and what MySQL will do with them. This guide lists them three ways on a real MySQL 9.7 database: for one table, for the whole database, and in reverse, every table that points at a given one.

Indexes decide how fast MySQL can find rows, and which values must be unique. SHOW INDEX lists them for one table, and INFORMATION_SCHEMA.STATISTICS lists them for a whole database, ready to filter. This guide runs both on a real MySQL 9.7 database created by EF Core, and explains how to read multi-column indexes, where the order of the columns matters.