SQL: Constraints

In SQL, constraints are rules or conditions that are applied to columns or tables to enforce data integrity and ensure that the data meets certain requirements. They define the limitations and restrictions on the data that can be stored in the database. Here are some common types of constraints in SQL along with examples:

Primary Key Constraint: Ensures the uniqueness and non-nullity of a column or a combination of columns, serving as a unique identifier for each row in a table.

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT
);

In this example, the PRIMARY KEY constraint is applied to the employee_id column, ensuring that each value is unique and not null.

Unique Constraint: Ensures the uniqueness of values within a column or a combination of columns but allows null values.

CREATE TABLE users (
  username VARCHAR(50) UNIQUE,
  email VARCHAR(50) UNIQUE
);

In this example, the UNIQUE constraint is applied to both the username and email columns, ensuring that each value is unique within its respective column.

Foreign Key Constraint: Establishes a relationship between two tables based on the values of a column, enforcing referential integrity.

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  order_date DATE,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

In this example, the FOREIGN KEY constraint is applied to the customer_id column in the orders table, referencing the customer_id column in the customers table. This ensures that the customer_id values in the orders table must exist in the customers table.

Not Null Constraint: Ensures that a column does not contain null values.

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  product_name VARCHAR(50) NOT NULL,
  price DECIMAL(8, 2) NOT NULL
);

In this example, the NOT NULL constraint is applied to both the product_name and price columns, ensuring that these columns must contain non-null values.

Check Constraint: Allows defining a custom condition that must be satisfied for a column’s value.

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT CHECK (age >= 18)
);

In this example, the CHECK constraint is applied to the age column, ensuring that the age is always greater than or equal to 18.

These are just a few examples of constraints in SQL. Constraints help maintain data integrity, enforce business rules, and provide a way to define relationships between tables, ensuring consistency and reliability in the database.

SQL: ALTER TABLE

ALTER TABLE is a SQL statement used to modify an existing table structure. It allows you to add, modify, or delete columns, change data types, set constraints, and perform other alterations on a table. Here’s an explanation of ALTER TABLE with examples:

Adding a column:

   ALTER TABLE employees
   ADD COLUMN salary INT;

This example adds a new column named “salary” of type INT to the “employees” table.

Modifying a column:

   ALTER TABLE employees
   MODIFY COLUMN salary DECIMAL(10,2);

This example modifies the “salary” column of the “employees” table and changes its data type to DECIMAL(10,2).

Renaming a column:

   ALTER TABLE employees
   RENAME COLUMN salary TO new_salary;

This example renames the “salary” column of the “employees” table to “new_salary”.

Deleting a column:

   ALTER TABLE employees
   DROP COLUMN new_salary;

This example removes the “new_salary” column from the “employees” table.

Adding a constraint:

   ALTER TABLE employees
   ADD CONSTRAINT pk_employee_id PRIMARY KEY (employee_id);

This example adds a primary key constraint named “pk_employee_id” on the “employee_id” column of the “employees” table.

Removing a constraint:

   ALTER TABLE employees
   DROP CONSTRAINT pk_employee_id;

This example removes the primary key constraint named “pk_employee_id” from the “employees” table.

These are just a few examples of what you can do with ALTER TABLE. The exact syntax and available options may vary depending on the database management system you are using.

SQL: Dropping ALTER TABLE

Dropping a table using the ALTER TABLE statement is not possible. Instead, you can drop an entire table using the DROP TABLE statement. The DROP TABLE statement removes an entire table and its associated data from the database. Here’s an example of how to drop a table:

DROP TABLE employees;

In this example, the “employees” table will be dropped, permanently removing it from the database. It is important to note that this action is irreversible, and all the data contained in the table will be lost.

Before executing the DROP TABLE statement, it is recommended to take a backup of the table or verify that you no longer need the data contained within it, as dropping a table will result in the permanent loss of all its data and cannot be undone.

SQL: RENAING ALTER TABLE

To rename a table using the ALTER TABLE statement, you can use the RENAME TO clause. Here’s an example of how to rename a table:

ALTER TABLE employees
RENAME TO staff;

In this example, the “employees” table is renamed to “staff”. After executing this statement, the table name will be changed, but the table structure and data will remain intact.

It’s important to note that when you rename a table, all the associated indexes, constraints, triggers, and dependencies will be automatically updated to reflect the new table name. However, if there are other database objects, such as views or stored procedures, that reference the table by its old name, you may need to update those objects manually.

Please be cautious when renaming a table, as it may impact other parts of your database or application that rely on the table’s name. It’s always a good practice to update any dependent objects to ensure the database remains consistent.

amit verma

Website | + posts

Technical content writer with data scientist, artificial intelligence, programming language, database. He has a bachelor’s degree in IT and a certificate in digital marketing, Digital transformation web development android app development He has written for website like Boomi techie, tech mantra, information hub, Tech all