Data Types & Constraints 🛡️

Two Ways to Learn: Visual Designer + SQL Code

Beginner Friendly 20 min read Lesson 3 of 13

Two Ways to Master Data Types & Constraints! 🎯

In this lesson, you'll learn two ways to work with data types and constraints: 1. The Visual Way (using SSMS right-click menus) and 2. The Code Way (writing SQL). Real pros know BOTH!

By the end of this lesson, you'll be able to:
  • ✅ Choose the right data type for your columns
  • ✅ Add constraints using SSMS Designer (right-click)
  • ✅ Add constraints using SQL code
  • ✅ Create tables with proper data types
  • ✅ Understand when to use each method
  • ✅ Build professional-grade databases!

Part 1: Data Types - Choosing the Right Box

What are Data Types? They tell SQL Server what kind of data can go in each column. Like choosing the right box for different items!
🖱️ Method 1: Visual (SSMS Designer)
  1. In Object Explorer, right-click Tables → New Table
  2. In the designer, for each column:
    • Type the Column Name
    • Choose Data Type from dropdown
    • Check Allow Nulls or uncheck it
  3. Press Ctrl + S to save, name your table
✅ No SQL needed! Everything with mouse clicks!
💻 Method 2: SQL Code
-- Create table with data types
CREATE TABLE Students (
    StudentId INT IDENTITY(1,1),
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Age INT,
    GPA DECIMAL(3,2),
    EnrolledDate DATE,
    IsActive BIT
);

📌 Key Data Types:

  • INT = Whole numbers (1, 2, 3)
  • NVARCHAR = Text (names, emails)
  • DECIMAL = Numbers with decimals (money)
  • DATE = Dates
  • BIT = True/False (0 or 1)
Data Type What It Stores Example When to Use
INT Whole numbers 25, 1000, -5 Age, quantity, ID
DECIMAL(10,2) Numbers with decimals 99.99, 15.50 Money, GPA, prices
NVARCHAR(50) Text (variable length) "John", "Smith" Names, emails, addresses
DATE Dates only 2024-01-15 Birthday, enrollment date
BIT True/False 0 or 1 Active, paid, confirmed
😂 Fun Joke: Why did the INT break up with the NVARCHAR? Because it couldn't handle the text messages! (Get it? Text? ...I'll stop 😅)

Part 2: Constraints - The Rules

What are Constraints? They're rules that keep your data clean and correct. Like a bouncer at a club - only valid data gets in!
🔑 PRIMARY KEY

What it does: Uniquely identifies each row (like an ID card)

Visual Way
  1. Open table in designer
  2. Right-click the column
  3. Select Set Primary Key
  4. A key icon appears! 🔑
Code Way
CREATE TABLE Students (
    StudentId INT PRIMARY KEY IDENTITY(1,1)
);
🚫 NOT NULL

What it does: Column cannot be empty (must have a value)

Visual Way
  1. Open table in designer
  2. Uncheck "Allow Nulls" box
  3. Column becomes required!
Code Way
FirstName NVARCHAR(50) NOT NULL
🆔 UNIQUE

What it does: No duplicate values allowed (like fingerprints)

Visual Way
  1. Open table in designer
  2. Right-click column → Indexes/Keys
  3. Click Add → Set Type = Unique Key
  4. In the grid, set Columns to your column
Code Way
Email NVARCHAR(100) UNIQUE
✅ CHECK

What it does: Validates data against a condition

Visual Way
  1. Open table in designer
  2. Right-click → Check Constraints
  3. Click Add
  4. Type expression: Age >= 18
Code Way
Age INT CHECK (Age >= 18)
🔗 FOREIGN KEY

What it does: Links to another table (like a relationship)

Visual Way
  1. Open table in designer
  2. Right-click → Relationships
  3. Click Add
  4. Choose Tables and Columns Specification
  5. Select the parent table and column
Code Way
DeptCode NVARCHAR(10),
CONSTRAINT FK_Students_Dept 
    FOREIGN KEY (DeptCode) 
    REFERENCES Departments(DeptCode)
😂 Fun Joke: Why did the FOREIGN KEY break up with the PRIMARY KEY? Because it was too dependent on it! (Relationship jokes are the best 😅)

Part 3: Adding Constraints Later

Good News: You don't need to plan everything upfront! You can add constraints to existing tables.
🖱️ Visual Way
  1. Right-click your table → Design
  2. Right-click in designer → Check Constraints
  3. Click Add → Type your expression
  4. Click Close → Save the table
  5. ✅ Constraint added!
✅ No SQL needed! Just right-click and add!
💻 Code Way
-- Add CHECK constraint to existing table
ALTER TABLE Students
ADD CONSTRAINT CK_Students_Age
CHECK (Age >= 18);

-- Add UNIQUE constraint
ALTER TABLE Students
ADD CONSTRAINT UQ_Students_Email
UNIQUE (Email);

📌 Why this is useful: You can add rules to existing data without deleting anything!

💡 Pro Tip: Always name your constraints (like CK_Students_Age) so you can easily identify them later!

Let's Practice! 🎮

Your Challenge: Create a Books table using BOTH visual and code methods!
Your Mission:
  1. Using SSMS Designer (Visual Way)
    • Create a new table called "Books"
    • Add columns: BookId, Title, Author, Year, Price
    • Set BookId as PRIMARY KEY
    • Set Title NOT NULL
    • Add CHECK constraint: Year >= 1900
    • Add CHECK constraint: Price > 0
  2. Using SQL (Code Way)
    • Write the SQL to create the same table
    • Include all constraints
    • Run the query to create it
💡 What You'll Learn:
  • ✅ How to use SSMS Designer
  • ✅ How to write SQL for tables
  • ✅ How to add PRIMARY KEY
  • ✅ How to add NOT NULL
  • ✅ How to add CHECK constraints
  • ✅ Both ways to become a pro!
Result: You'll master BOTH methods!

🎉 What You Learned Today!

Data Types
INT, NVARCHAR, DATE
PRIMARY KEY
Unique IDs
NOT NULL
Required columns
CHECK
Validate data
Visual Way
SSMS Designer
Code Way
SQL Queries
Master!
Both ways!

🎯 You're now a master of data types and constraints - using BOTH visual and code methods!

Previous Lesson Next Lesson
Lessons
Test Your Knowledge - Take Quiz