top of page

Data Types, Primary-Foreign Keys & Constraints in SQL

In SQL (Structured Query Language), data types, primary keys, foreign keys, and constraints are crucial concepts for designing and maintaining a relational database. Let's discuss each of these concepts:


Data Types, Primary-Foreign Keys & Constraints in SQL

Data Types:

  • Data types define the type of data that can be stored in a column of a table. Each column in a table must have a specific data type. Common data types include:

  • Numeric Types: INT, SMALLINT, BIGINT, DECIMAL, FLOAT, etc.

  • Character Strings: CHAR, VARCHAR, TEXT, etc.

  • Date and Time Types: DATE, TIME, DATETIME, TIMESTAMP, etc.

  • Boolean Types: BOOLEAN, BIT, etc.

Example of creating a table with data types:

CREATE TABLE Employees (

    EmployeeID INT,

    FirstName VARCHAR(50),

    LastName VARCHAR(50),

    HireDate DATE

);

Primary Key:

  • A primary key is a column or a set of columns that uniquely identifies each row in a table. It must have unique values, and it cannot contain NULL values.

  • The primary key is used to establish relationships between tables.

  • Typically, the primary key is created when the table is defined using the PRIMARY KEY constraint.


Example of creating a table with a primary key:

CREATE TABLE Students (

    StudentID INT PRIMARY KEY,

    FirstName VARCHAR(50),

    LastName VARCHAR(50),

    BirthDate DATE

);


Foreign Key:

  • A foreign key is a column or a set of columns in a table that refers to the primary key in another table. It establishes a link between the two tables, creating a relationship.

  • Foreign keys help maintain data integrity and enforce referential integrity between tables.

  • The foreign key is created using the FOREIGN KEY constraint.


Example of creating a table with a foreign key:

CREATE TABLE Orders (

    OrderID INT PRIMARY KEY,

    ProductID INT,

    Quantity INT,

    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)

);


Constraints:

  • Constraints are rules or conditions applied to the data columns in a table. They enforce the data integrity of the database.

  • Common constraints include NOT NULL, UNIQUE, CHECK, and DEFAULT.

  • Constraints are specified at the time of table creation or can be added later using the ALTER TABLE statement.


Example of creating a table with constraints:

CREATE TABLE Customers (

    CustomerID INT PRIMARY KEY,

    FirstName VARCHAR(50) NOT NULL,

    LastName VARCHAR(50) NOT NULL,

    Email VARCHAR(100) UNIQUE,

    Age INT CHECK (Age >= 18),

    RegistrationDate DATE DEFAULT CURRENT_DATE

);


These concepts are fundamental for designing a well-structured and normalized relational database, ensuring data accuracy, consistency, and relationships between tables.


תגובות


bottom of page