SQL
Constraints
Constraints can be specified when the table is created with the CREATE TABLE
statement, or after the table is created with the ALTER TABLE statement.
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 NOT NULL Constraint
SQL NOT NULL on CREATE TABLE
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Age int
);
SQL NOT NULL on ALTER TABLE
ALTER TABLE Persons
MODIFY Age int NOT NULL;
SQL UNIQUE Constraint
CREATE TABLE Persons (
ID int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);
SQL PRIMARY KEY Constraint
CREATE TABLE Persons (
ID int NOT NULL PRIMARY KEY,
LastName varchar(255) primary key,
FirstName varchar(255),
Age int
);
SQL FOREIGN KEY Constraint
A FOREIGN KEY is a key used to link two tables together.
A FOREIGN KEY is a field (or collection of fields) in one table that refers to the
PRIMARY KEY in another table.
CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);
SQL CHECK Constraint
The CHECK constraint is used to limit the value range that can be placed in a
column.
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int, not null
CHECK (Age>=18)
);
SQL DEFAULT Constraint
The DEFAULT constraint is used to provide a default value for a column.
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255) DEFAULT 'delhi'
);
ALTER TABLE Persons
ALTER City DROP DEFAULT;
LEFT JOIN Keyword
The LEFT JOIN keyword returns all records from the left table (table1), and the
matched records from the right table (table2). The result is NULL from the right
side, if there is no match.
In some databases LEFT JOIN is called LEFT OUTER JOIN.
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;
SQL RIGHT JOIN Keyword
The RIGHT JOIN keyword returns all records from the right table (table2), and
the matched records from the left table (table1). The result is NULL from the
left side, when there is no match.
In some databases RIGHT JOIN is called RIGHT OUTER JOIN.
SELECT Orders.OrderID, Employees.LastName, Employees.FirstName
FROM Orders
RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY Orders.OrderID;
SQL CREATE INDEX Statement
Indexes are used to retrieve data from the database very fast. The users cannot
see the indexes, they are just used to speed up searches/queries.
As they create a separate name data structure hence occupy memory for
storage.
CREATE INDEX Syntax
Creates a unique index on a table. Duplicate values are not allowed:
On a already created table.
CREATE INDEX idx_lastname
ON Persons (LastName);
7. CREATE INDEX HIREDATE ON EMP1(NAME);
With create table command
8. CREATE TABLE POI( pname varchar (20) not null,
Pid integer(4) primary key,
Count integer(10) not null,
Category varchar(10),
Index ct(Category));
SHOW INDEXES FROM POI; // TO SHOW INDEX
DROP INDEX CT ON POI ; // TO DROP INDEX
ALTER TABLE POI RENAME CT TO CATE; // RENAME INDEX
