SQL for .NET developers Beginner

Databases, tables and keys

Almost every MVC app stores its data in a relational database such as SQL Server. This lesson explains the building blocks: databases, tables, rows, columns, data types, primary keys and foreign keys, and shows how to create two related tables.

How relational data is organized
  1. Database: ShopDbA container for related tables
  2. Table: CustomersOne row per customer, one column per piece of information
  3. Primary key: Customers.CustomerIdA unique value that identifies each row
  4. Foreign key: Orders.CustomerIdEach order points to the customer who placed it
  5. Table: OrdersOne row per order, linked to Customers

The idea in plain words

A relational database stores data in tables, which look like spreadsheets with strict rules. Each column has a name and a data type, such as int, nvarchar(100) for text or decimal(10,2) for money. Each row is one record.

Every table should have a primary key: a column (or columns) whose value is unique and never empty, usually an auto-numbered id. Tables are connected with foreign keys. An Orders table has a CustomerId column that must match an existing CustomerId in Customers, so an order can never point to a customer who does not exist.

Example: two related tables

IDENTITY(1,1) makes SQL Server number new rows automatically. The constraints protect the data even if application code has a bug.

SQL
CREATE TABLE Customers (
    CustomerId  int IDENTITY(1,1) PRIMARY KEY,
    Name        nvarchar(100) NOT NULL,
    Email       nvarchar(256) NOT NULL UNIQUE,
    CreatedAt   datetime2     NOT NULL DEFAULT SYSUTCDATETIME()
);

CREATE TABLE Orders (
    OrderId     int IDENTITY(1,1) PRIMARY KEY,
    CustomerId  int NOT NULL
        REFERENCES Customers (CustomerId),   -- foreign key
    Total       decimal(10,2) NOT NULL CHECK (Total >= 0),
    OrderedAt   datetime2 NOT NULL
);

Common constraints

  • NOT NULL: the column must always have a value.
  • UNIQUE: no two rows may share the value, such as an email address.
  • CHECK: a rule each row must pass, such as Total >= 0.
  • DEFAULT: the value used when an insert does not provide one.

Common mistakes

  • Using float for money; use decimal so values are exact.
  • Skipping foreign keys, which lets orphaned rows creep in.
  • Storing several values in one column, such as a comma-separated list of phone numbers. Use another table.
  • Using nvarchar(max) everywhere instead of sensible lengths.

Interview question

What is the difference between a primary key and a foreign key?

A primary key uniquely identifies each row of its own table; it must be unique and not null, and a table has only one. A foreign key is a column in one table that references the primary key (or a unique key) of another table, enforcing that the referenced row exists. Together they model relationships such as one customer having many orders.

Your progress is saved only in this browser.

Want the full details? Read the official Microsoft documentation for this topic.