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.
- Database: ShopDbA container for related tables
- Table: CustomersOne row per customer, one column per piece of information
- Primary key: Customers.CustomerIdA unique value that identifies each row
- Foreign key: Orders.CustomerIdEach order points to the customer who placed it
- 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.
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.
Want the full details? Read the official Microsoft documentation for this topic.