SQL Tutorial

SQL Clauses / Operators

SQL-Injection

SQL Functions

SQL Queries

PL/SQL

MySQL

SQL Server

Misc

SQL | Constraints

In SQL, constraints are used to specify rules for data in a table. Constraints are applied to ensure the accuracy and reliability of data in the database. They can be specified when the table is created (using the CREATE TABLE statement) or after the table is created (using the ALTER TABLE statement).

Here are the most commonly used constraints in SQL:

1. PRIMARY KEY Constraint:

  • It uniquely identifies each record in a table.
  • Primary keys must contain unique values and cannot contain NULL values.
  • A table can have only one primary key, which may consist of a single or multiple columns.

Example:

CREATE TABLE Persons (
    ID INT NOT NULL,
    LastName VARCHAR(255),
    FirstName VARCHAR(255),
    PRIMARY KEY (ID)
);

2. UNIQUE Constraint:

  • It ensures that all values in a column are unique.
  • Unlike primary keys, a table can have multiple unique constraints.

Example:

CREATE TABLE Persons (
    ID INT NOT NULL UNIQUE,
    LastName VARCHAR(255) UNIQUE,
    FirstName VARCHAR(255)
);

3. FOREIGN KEY Constraint:

  • It is used to ensure referential integrity of data in one table to match values in another table.
  • A foreign key in one table points to a primary key in another table.

Example:

CREATE TABLE Orders (
    OrderID INT NOT NULL,
    OrderNumber INT,
    PersonID INT,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (PersonID) REFERENCES Persons(ID)
);

4. CHECK Constraint:

  • It ensures that all values in a column satisfy certain conditions.
  • It allows for more complex expressions compared to other constraints.

Example:

CREATE TABLE Persons (
    ID INT NOT NULL,
    Age INT,
    CHECK (Age >= 18)
);

5. DEFAULT Constraint:

  • It sets a default value for a column when none is specified.

Example:

CREATE TABLE Persons (
    ID INT NOT NULL,
    CountryName VARCHAR(255) DEFAULT 'USA'
);

6. NOT NULL Constraint:

  • It ensures that a column cannot have a NULL value.

Example:

CREATE TABLE Persons (
    ID INT NOT NULL,
    LastName VARCHAR(255) NOT NULL,
    FirstName VARCHAR(255)
);

7. INDEX:

  • While not a constraint in the traditional sense, indexes are used to retrieve data from databases more quickly. They can be created on one or multiple columns.

Example:

CREATE INDEX idx_lastname
ON Persons (LastName);

Points to Consider:

  • Once a constraint is set, the SQL database system checks every operation (e.g., INSERT, UPDATE) to ensure data integrity. If a data operation violates a constraint, it is rolled back.

  • If you're trying to add a constraint to an existing table with data, ensure that the current data meets the requirements of the constraint. For instance, if you're trying to add a UNIQUE constraint to a column that already has duplicate values, you will encounter an error.

  • It's essential to choose the right constraints for the requirements of your application. Overconstraining can be as problematic as underconstraining.

  • Constraint names should be meaningful, and a naming convention can be helpful, especially in larger databases.

  • The specifics of constraints can vary among different RDBMSs, so always refer to the documentation of the particular system you're using.

  1. How to Create Constraints in SQL: Constraints are defined during table creation or added later using ALTER TABLE.

    CREATE TABLE your_table (
   column1 INT PRIMARY KEY,
   column2 VARCHAR(50) NOT NULL,
   column3 INT UNIQUE
);

  2. Primary Key Constraints in SQL:

    CREATE TABLE your_table (
   user_id INT PRIMARY KEY,
   username VARCHAR(50) NOT NULL
);

  3. Foreign Key Constraints in SQL:

    CREATE TABLE orders (
   order_id INT PRIMARY KEY,
   product_id INT,
   FOREIGN KEY (product_id) REFERENCES products(product_id)
);

  4. Unique Constraints in SQL:

    CREATE TABLE your_table (
   email VARCHAR(50) UNIQUE,
   -- other columns
);

  5. Check Constraints in SQL:

    CREATE TABLE your_table (
   age INT CHECK (age >= 18),
   -- other columns
);

  6. Default Constraints in SQL:

    CREATE TABLE your_table (
   registration_date DATE DEFAULT GETDATE(),
   -- other columns
);

  7. Constraints and Data Integrity in SQL: Constraints ensure data integrity by enforcing rules on the data stored in tables.

  8. Modifying and Dropping Constraints in SQL:

    ALTER TABLE your_table
ADD CONSTRAINT pk_your_table PRIMARY KEY (column1);

ALTER TABLE your_table
DROP CONSTRAINT pk_your_table;

  9. Naming Conventions for Constraints in SQL: It's a good practice to name constraints descriptively.

    CREATE TABLE your_table (
   user_id INT CONSTRAINT pk_users PRIMARY KEY,
   -- other columns
);

  10. Constraints with ALTER TABLE in SQL:

    ALTER TABLE your_table
ADD CONSTRAINT fk_your_table
FOREIGN KEY (column1) REFERENCES other_table(column2);

  11. Constraints vs. Triggers in SQL: Constraints and triggers serve different purposes. Constraints are declarative rules, while triggers are procedural code executed in response to events.

Copyright © devanddep.com | All Rights Reserved