a stack of stacked blue and white plates

SQL Tutorial: Datatypes, Primary Key, Foreign Key, and Constraints in SQL

Introduction

SQL (Structured Query Language) is a programming language used for managing and manipulating relational databases. In this tutorial, we will explore some important concepts in SQL, including datatypes, primary keys, foreign keys, and constraints.

Datatypes in SQL

Datatypes define the type of data that can be stored in a column of a database table. SQL supports various datatypes, such as:

  • Integer: Used to store whole numbers.
  • Float: Used to store floating-point numbers.
  • Char: Used to store fixed-length character strings.
  • Varchar: Used to store variable-length character strings.
  • Date: Used to store dates.
  • Time: Used to store time values.
  • Boolean: Used to store true/false values.

It is important to choose the appropriate datatype for each column in order to ensure data integrity and optimize storage space.

Primary Key

A primary key is a column or a set of columns that uniquely identifies each record in a table. It ensures that each row in a table is unique and can be used to establish relationships with other tables.

To define a primary key in SQL, the PRIMARY KEY constraint is used. For example, consider a table called “Customers” with a primary key on the “CustomerID” column:

CREATE TABLE Customers (
  CustomerID INT PRIMARY KEY,
  FirstName VARCHAR(50),
  LastName VARCHAR(50),
  Email VARCHAR(100)
);

In this example, the “CustomerID” column is designated as the primary key. This means that each customer record in the table must have a unique value in the “CustomerID” column.

Foreign Key

A foreign key is a column or a set of columns in one table that refers to the primary key in another table. It establishes a relationship between two tables and ensures referential integrity.

To define a foreign key in SQL, the FOREIGN KEY constraint is used. For example, consider two tables: “Orders” and “Customers”. The “Orders” table has a foreign key on the “CustomerID” column, which references the primary key in the “Customers” table:

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

In this example, the “CustomerID” column in the “Orders” table is a foreign key that references the primary key in the “Customers” table. This ensures that each order is associated with a valid customer.

Constraints

Constraints are rules that are applied to columns or tables to enforce data integrity and maintain consistency. Some commonly used constraints in SQL include:

  • NOT NULL: Ensures that a column cannot have a NULL value.
  • UNIQUE: Ensures that each value in a column is unique.
  • DEFAULT: Specifies a default value for a column when no value is provided.
  • CHECK: Defines a condition that must be satisfied for the data in a column.

For example, consider a table called “Employees” with a constraint on the “Salary” column to ensure that the salary is greater than or equal to 0:

CREATE TABLE Employees (
  EmployeeID INT PRIMARY KEY,
  FirstName VARCHAR(50),
  LastName VARCHAR(50),
  Salary DECIMAL(10,2) CHECK (Salary >= 0)
);

In this example, the CHECK constraint is used to enforce the condition that the salary must be greater than or equal to 0.

Conclusion

In this tutorial, we covered some important concepts in SQL, including datatypes, primary keys, foreign keys, and constraints. Understanding these concepts is crucial for designing and managing relational databases effectively. By using the appropriate datatypes, defining primary and foreign keys, and applying constraints, you can ensure data integrity and maintain consistency in your SQL databases.

Leave a Comment