sql constraints

SQL constraints are used to specify rules for the data in a table. These are used to limit the type of data in a table. Constraints ensure the accuracy and reliability of the data in the database.

SQL constraints are

NOT NULL : It make sure that a column cannot store NULL value

e.g CREATE table CUSTOMER (customer_id int NOT NULL , Name varchar(60));

UNIQUE : It make sure that each row for a column must have unique value

e.g CREATE table CUSTOMER (customer_id int NOT NULL UNIQUE, Name varchar(60));

PRIMARY KEY : Primary key is one of the important constraints.It is make sure that each row of a column is unique and not null. Primary key uniquely identifies each record in a database.

e.g CREATE table CUSTOMER (customer_id int NOT NULL PRIMARY KEY, Name varchar(60));

FOREIGN KEY: Foreign key is used to relate two tables, A foreign key is a field in a table that matches another field(primary key) of another table.

e.g CREATE table Order_info(order_id int PRIMARY KEY,product_name varchar(60) NOT NULL,
c_id int FOREIGN KEY REFERENCES CUSTOMER (customer_id));

CHECK : CHECK is used to restrict the value of a column between a given range.

e.g CREATE table CUSTOMER (customer_id int NOT NULL CHECK(s_id > 100)
, Name varchar(60));

What is SQL

SQL (structured query language) is the set of instructions used to interact with a relational database System. It is used for storing, manipulating and retrieving data stored in relational database. SQL is the standard language for Relation Database System.

SQL has three major components.

1) Data Definition Language (DDL)

Data Definition Language used to create and modify the structure of database objects in a database. These database objects include views, schemas, tables etc.

Commonly used Data Definition Languages are:

CREATE: This is used to create a new table, It has its own predefined syntax

e.g CREATE customer (customer_id int NOT NULL AUTO_INCREMENT, first_name varchar(255), email varchar(255),address varchar(255), PRIMARY KEY (customer_id) );

ALTER : This command is used to modify existing database table structure
e.g ALTER TABLE customer AUTO_INCREMENT=100

DROP : A drop command deletes a table, index etc
e.g DROP TABLE customer

TRUNCATE : This command is used to empty a table

e.g TRUNCATE TABLE customer
RENAME : This command is used to rename a table
e.g RENAME customer TO customers_info

COMMENT : This statement is used to add a comment about a table or table column .

e.g COMMENT ON COLUMN customer.address IS ‘only customer city name’;

2) Data Manipulation Language (DML)

As the name specify data manipulation language is permitting users to manipulate data in a database .Common Manipulation operation are insert records into table, or need to change some record, or delete some record or perform any other actions on records in the database.

Commonly used Data Manipulation Languages are:

INSERT: INSERT is used to insert data in to database

e.g INSERT TO CUSTOMER(first_name, email, address) VALUES (‘sachin’,’[email protected]’,’Mubai’)

SELECT : SELECT is used to retrive data from database

e.g SELECT * FROM customer

UPDATE : UPDATE is used to updates existing data within a table

e.g UPDATE CUSTOMER SET first_name=’sachin ramesh’ WHERE email=’[email protected]’

DELETE : DELETE is used to DELETE existing data within a table

e.g DELETE FROM CUSTOMER WHERE email=’[email protected]

CALL: call a PL/SQL or Java subprogram

CALL my_procedure(arg1 => 3, arg2 => 4)

LOCK TABLE : concurrency Control

e.g LOCK TABLES customer READ;

3) Data Control Language (DCL)

Data Control Language command is used to control access to data stored in a database. It is used to decide who can view this table and what kind of access like read, write, delete should be given to the other users.

Commonly used Data Control Languages are:

GRANT : GRANT provides the privileges to the users on the database.

GRANT INSERT ON customer TO vijay;

REVOKE : Revoke is used to cancel previously granted or denied permissions

e.g REVOKE INSERT ON customer FROM vijay;