45 posts tagged with "Database"

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.

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.

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.