55 posts tagged with "MySQL"

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.

You save DateTime.UtcNow with EF Core, read it back from MySQL, and the Z that marks UTC is gone from your API's JSON. Worse, a DateTime.Now slipped in somewhere is stored as local time, hours away from everything else. This guide shows both problems with a real .NET C# Web API and MySQL 9.7, explains why they happen, and fixes them for every DateTime in the model with one small value converter.

Money belongs in a decimal, never a double, and the database column needs a precision you chose on purpose. This guide shows what MySQL column EF Core creates for a decimal when you don't configure it, how MySQL handles values that don't fit, and the three ways to set the precision: one property, an attribute, or one rule for every decimal in the model.

By default, EF Core saves a C# enum as its number: Sale becomes 2. That works, but anyone reading the table needs the C# code to know what 2 means, and reordering the enum silently changes the meaning of every old row. This guide stores enums as their names instead, first for one property and then for every enum at once, and handles the part most guides skip: the rows that already hold numbers.

Every .NET C# app that talks to MySQL starts with a connection string: one line that says where the server is, which database to use, who you are, and how to protect the connection. This guide builds one for Oracle's MySql.Data driver, which EF Core's MySql.EntityFrameworkCore provider also uses, explains each key with its documented default, and tests it with a small C# program that tells you exactly how it connected.

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.