Data Types and Constraints

Ever tried to fit a square peg into a round hole? That's what it's like when data doesn't match its type in SQL. Today, we're going to explore data types and constraints to make sure your data fits just right.

Table of Contents

1. Common Data Types
2. NULL Values
3. Constraints
4. DEFAULT Values

Common Data Types

In SQL, data types define the kind of data you can store in a column. Choosing the right data type is like picking the right container for your stuff. You wouldn't put soup in a paper bag, right?

Numeric Data Types

• INT: Whole numbers, like 42 or -7.
• FLOAT: Floating-point numbers, like 3.14.
• DECIMAL(p,s): Precise numbers with fixed decimal points.

String Data Types

• CHAR(n): Fixed-length strings. If you specify CHAR(5), it always stores 5 characters.
• VARCHAR(n): Variable-length strings, up to n characters.
• TEXT: Large text data.

Date and Time Data Types

• DATE: Stores dates, like '2024-10-18'.
• TIME: Stores time, like '14:30:00'.
• DATETIME: Stores both date and time.

Boolean Data Type

• BOOLEAN: Stores TRUE or FALSE values.

Tip: Always choose the most appropriate data type. It helps with data integrity and can improve performance.

NULL Values

Have you ever left a field blank on a form? That's kind of like a NULL value in SQL. It represents missing or unknown data.

Understanding NULL

NULL isn't zero or an empty string. It's the absence of any value.

Checking for NULL

To check if a value is NULL, you use IS NULL or IS NOT NULL.

SELECT * FROM Customers WHERE PhoneNumber IS NULL;

This query fetches customers who haven't provided a phone number.

Constraints

Constraints are rules you set on your tables to keep your data accurate and reliable. Think of them as the bouncers at the club door, letting in only the right data.

Primary Key Constraint

A primary key uniquely identifies each record in a table.

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(100)
);

Foreign Key Constraint

A foreign key links records between tables.

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderDate DATE,
    CustomerID INT,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

Unique Constraint

Ensures that all values in a column are unique.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Email VARCHAR(100) UNIQUE
);

Not Null Constraint

Ensures that a column cannot have a NULL value.

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100) NOT NULL
);

Check Constraint

Sets a condition that each row must satisfy.

CREATE TABLE Accounts (
    AccountID INT PRIMARY KEY,
    Balance DECIMAL(10,2),
    CHECK (Balance >= 0)
);

DEFAULT Values

Sometimes, you want a column to have a default value if none is provided. It's like setting the default ringtone on your phone.

Setting Default Values

CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    Username VARCHAR(50),
    SignupDate DATE DEFAULT CURRENT_DATE
);

If you insert a new user without specifying a SignupDate, it defaults to the current date.