Integrity Constraints, SQL Queries

SQL is a programming language for Relational Databases. It is designed over relational algebra and tuple relational calculus. SQL comes as a package with all major distributions of RDBMS.

SQL comprises both data definition and data manipulation languages. Using the data definition properties of SQL, one can design and modify database schema, whereas data manipulation properties allows SQL to store and retrieve data from database.

SQL constraints are used to specify rules for the data in a table.

Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action, the action is aborted.

Constraints can be column level or table level. Column level constraints apply to a column, and table level constraints apply to the whole table.

The following constraints are commonly used in SQL:

• NOT NULL – Ensures that a column cannot have a NULL value
• UNIQUE – Ensures that all values in a column are different
• PRIMARY KEY – A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table
• FOREIGN KEY – Uniquely identifies a row/record in another table
• CHECK – Ensures that all values in a column satisfies a specific condition
• DEFAULT – Sets a default value for a column when no value is specified
• INDEX – Used to create and retrieve data from the database very quickly

SQL Queries

SQL uses the following set of commands to define database schema −

CREATE

Creates new databases, tables and views from RDBMS.

For example −

Create database tutorialspoint;Create table article;Create view for_students;

DROP

Drops commands, views, tables, and databases from RDBMS.

For example−

Drop object_type object_name;Drop database tutorialspoint;Drop table article;Drop view for_students;

ALTER

Modifies database schema.

Alter object_type object_name parameters;

For example−

Alter table article add subject varchar;

This command adds an attribute in the relation article with the name subjectof string type.