Integrity constraints ensure that changes made to the database by authorized users do not result in a loss of data consistency.
An instructor name cannot be null.
No two instructors can have the same instructor ID
Every department name in the course relation must have a matching department name in the department relation.
The budget of a department must be greater than $0.00
Constraints on a Single Relation:
primary-key constraint
not null
unique
check(
Not Null Constraint
The null value is a member of all domains, and as a result is a legal value for every attribute in SQL by default.
In cases such as this, we wish to forbid null values, and we can do so by declaring it as follows:
name varchar(20) not null
budget numeric(12,2) not null
The not null specification prohibits the insertion of a null value for the attribute.
A primary key cannot take null values so it doesn't need to be declared as not null.
Unique Constraint
The unique specification says that attributes Aj1, Aj2, ... , Ajm form a candidate key; that is, no two tuples in the relation can be equal on all the listed attributes.
However, candidate key attributes are permitted to be null unless they have explicitly been declared to be not null.
The check Clause
When applied to a relation declaration, the clause check(P) specifies a predicate P that must be satisfied by every tuple in a relation.
A common use of the check clause is to ensure that attribute values satisfy specified conditions
For instance, a clause check (budget > 0) in the create table command for relation department would ensure that the value of budget is non-negative.
create table section (courseid varchar (8), semester varchar (6), primary key (courseid), check (semester in (’Fall’, ’Winter’, ’Spring’, ’Summer’)));
Often, we wish to ensure that a value that appears in one relation for a given set of attributes also appears for a certain set of attributes in another relation. This condition is called referential integrity.
An Example of this is Foreign key
The definition of the course table has a declaration “foreign key (deptname) references department”.
This foreign-key declaration specifies that for each course tuple, the department name specified in the tuple must exist in the department relation.
Without this constraint, it is possible for a course to specify a nonexistent department name.
By default, in SQL a foreign key references the primary-key attributes of the referenced table.
Attributes of foreign keys are allowed to be null, provided that they have not otherwise been declared to be not null.
SQL Assertions :
An assertion is a predicate expressing a condition that we wish the database always to satisfy.
Domain constraints and referential-integrity constraints are special forms of assertions.
An assertion in SQL takes the form:
create assertion <assertion-name> check <predicate>;
When an assertion is created, the system tests it for validity. If the assertion is valid, then any future modification to the database is allowed only if it does not cause that assertion to be violated. This testing may introduce a significant amount of overhead if complex assertions have been made.
Hence, assertions should be used with great care. The high overhead of testing and maintaining assertions has led some system developers to omit support for general assertions, or to provide specialized forms of assertion that are easier to test.
Q. For each tuple in the student relation, the value of the attribute tot_cred must equal the sum of credits of courses that the student has completed successfully. [ Hint : student (ID, name, dept_name, tot_cred)
create assertion credits earned constraint check
(not exists (select ID
from student
where tot_cred <> (select sum(credits)
from takes natural join course
where student.ID = takes.ID
and grade is not null and grade <> ’F’ )There are additional built-in data types supported by SQL such as:
Date and Time Types in SQL :
date: A calendar date containing a (four-digit) year, month, and day of the month.
time: The time of day, in hours, minutes, and seconds.
timestamp: A combination of date and time.
Default Values :
create table student ( ID varchar (5), cred numeric (3,0) default 0, primary key (ID));
While inserting values in the relation we can omit the default attribute such as:
insert into student(ID) values (’12789’);
Index Creation :
An index on an attribute of a relation is a data structure that allows the database system to find those tuples in the relation that have a specified value for that attribute efficiently, without scanning through all the tuples of the relation.
create index studentID_index on student(ID);
Large-Object Types :
Many current-generation database applications need to store attributes that can be large (of the order of many kilobytes), such as a photograph, or very large (of the order of many megabytes or even gigabytes), such as a high-resolution medical image or video clip.
SQL therefore provides large-object data types for character data (clob) and binary data (blob). The letters “lob” in these data types stand for “Large OBject.”
book_review clob(10KB) image blob(10MB) movie blob(2GB)
User-Defined Types :
The create type clause can be used to define new types.
create type Dollars as numeric(12,2) final; create type Pounds as numeric(12,2) final;
The newly created types can then be used, for example, as types of attributes of relations.
create table department (dept_name varchar (20), building varchar (15), budget Dollars);
Q. Date and time values can be specified like this:
date ’2001-04-25’
time ’09:30:00’
timestamp ’2001-04-25 10:29:01.45’