SQL Data Types and Constraints Tutorial

1. SQL Data Types

SQL provides various data types to store different types of information. Here are some of the most commonly used data types:

• Integer Types:
  • TINYINT: Very small integer (1 byte).
  • SMALLINT: Small integer (2 bytes).
  • MEDIUMINT: Medium-sized integer (3 bytes).
  • INT or INTEGER: Normal-sized integer (4 bytes).
  • BIGINT: Large integer (8 bytes).
• Floating Point Types:
  • FLOAT: Floating-point number (4 bytes).
  • DOUBLE: Double-precision floating-point number (8 bytes).
  • DECIMAL(p,s): Fixed-point number where p is the total number of digits and s is the number of digits after the decimal point.
• String Types:
  • CHAR(n): Fixed-length string (n characters).
  • VARCHAR(n): Variable-length string (up to n characters).
  • TINYTEXT: Very small text (up to 255 characters).
  • TEXT: Small text (up to 65,535 characters).
  • MEDIUMTEXT: Medium text (up to 16,777,215 characters).
  • LONGTEXT: Large text (up to 4,294,967,295 characters).
• Date and Time Types:
  • DATE: Date value (YYYY-MM-DD).
  • TIME: Time value (HH:MM:SS).
  • DATETIME: Date and time value (YYYY-MM-DD HH:MM:SS).
  • TIMESTAMP: Timestamp value (auto-updating).
  • YEAR: Year value (YYYY).

2. SQL Constraints

Constraints are rules that limit the type of data that can go into a table, ensuring accuracy and reliability. The main types of constraints are:

• NOT NULL: Ensures that a column cannot have a NULL value.
• UNIQUE: Ensures that all values in a column are unique.
• PRIMARY KEY: Uniquely identifies each row in a table. A primary key column cannot contain NULL values.
• FOREIGN KEY: Ensures referential integrity by linking two tables. A foreign key in one table points to a primary key in another table.
• CHECK: Ensures that all values in a column satisfy a specific condition.
• DEFAULT: Sets a default value for a column when no value is specified.

3. Using Data Types and Constraints in Table Creation

Here’s an example of how to create a table using various data types and constraints:

Edit Copy

CREATE TABLE Customers (
    CustomerID INT NOT NULL PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    DateOfBirth DATE,
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

This SQL statement creates a Customers table with constraints ensuring unique emails and non-null first and last names.

4. Conclusion

Understanding SQL data types and constraints is crucial for effective database design. Properly defining data types and implementing constraints helps maintain data integrity and optimize performance.