MS SQL — Tables, Keys & Relations

Database & Table

CREATE DATABASE Shop;
USE Shop;

CREATE TABLE Categories (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(100) NOT NULL UNIQUE,
    CreatedAt DATETIME2 DEFAULT GETDATE()
);

CREATE TABLE Products (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(200) NOT NULL,
    Price DECIMAL(18,2) NOT NULL CHECK (Price >= 0),
    Stock INT DEFAULT 0,
    CategoryId INT NOT NULL,
    CreatedAt DATETIME2 DEFAULT GETDATE(),

    CONSTRAINT FK_Products_Categories
        FOREIGN KEY (CategoryId) REFERENCES Categories(Id)
        ON DELETE NO ACTION ON UPDATE CASCADE,

    INDEX IX_Products_Name (Name),
    INDEX IX_Products_Category (CategoryId)
);

Data Types (MS SQL)

Category	Types
Integer	TINYINT, SMALLINT, INT, BIGINT
Decimal	DECIMAL(p,s), NUMERIC, MONEY, FLOAT, REAL
String	CHAR, VARCHAR, NVARCHAR, NCHAR, TEXT
Date/Time	DATE, TIME, DATETIME2 ⭐, DATETIMEOFFSET
Binary	BINARY, VARBINARY, IMAGE
Other	BIT, UNIQUEIDENTIFIER, XML, JSON

💡 NVARCHAR ব্যবহার করুন (Unicode, বাংলা সমর্থন)। VARCHAR শুধু ASCII।

Constraints

CREATE TABLE Users (
    Id INT IDENTITY PRIMARY KEY,
    Email NVARCHAR(150) NOT NULL UNIQUE,         -- duplicate বন্ধ
    Username NVARCHAR(50) NOT NULL,
    Age INT CHECK (Age >= 18 AND Age <= 120),    -- range
    Status NVARCHAR(20) DEFAULT 'active'
        CHECK (Status IN ('active', 'inactive', 'banned'))
);

Relationships

One-to-Many

-- One Category → Many Products
CategoryId INT FOREIGN KEY REFERENCES Categories(Id)

Many-to-Many (Junction Table)

CREATE TABLE Students (
    Id INT IDENTITY PRIMARY KEY,
    Name NVARCHAR(100)
);
CREATE TABLE Courses (
    Id INT IDENTITY PRIMARY KEY,
    Title NVARCHAR(200)
);
CREATE TABLE Enrollments (
    StudentId INT NOT NULL,
    CourseId INT NOT NULL,
    EnrolledAt DATETIME2 DEFAULT GETDATE(),
    PRIMARY KEY (StudentId, CourseId),
    FOREIGN KEY (StudentId) REFERENCES Students(Id),
    FOREIGN KEY (CourseId) REFERENCES Courses(Id)
);

One-to-One

CREATE TABLE Profiles (
    UserId INT PRIMARY KEY,        -- PK + FK same
    Bio NVARCHAR(MAX),
    AvatarUrl NVARCHAR(500),
    FOREIGN KEY (UserId) REFERENCES Users(Id)
);

ON DELETE / ON UPDATE

• NO ACTION (default) — referenced থাকলে delete fail
• CASCADE — parent মুছলে children সব মুছবে
• SET NULL — FK NULL হবে
• SET DEFAULT — default value

ALTER TABLE

ALTER TABLE Products ADD Discount DECIMAL(5,2) DEFAULT 0;
ALTER TABLE Products DROP COLUMN Discount;
ALTER TABLE Products ALTER COLUMN Name NVARCHAR(300);
ALTER TABLE Products ADD CONSTRAINT UQ_Products_Name UNIQUE (Name);

EF Core → SQL Mapping

// Fluent API
mb.Entity<Product>()
    .HasOne(p => p.Category)
    .WithMany(c => c.Products)
    .HasForeignKey(p => p.CategoryId)
    .OnDelete(DeleteBehavior.Restrict);

mb.Entity<Product>().HasIndex(p => p.Name);
mb.Entity<Product>().Property(p => p.Price).HasPrecision(18, 2);