Constraints
SQL provides a way to place constraints on the attributes
of a table when it is created.
Here’s a few CREATE TABLE
statements with lots of
constraints.
CREATE TABLE R(A INT, B INT, C TEXT NOT NULL, D TEXT UNIQUE, PRIMARY KEY(A, B), FOREIGN KEY(D) REFERENCES T(X)) ; create table Highschooler(ID int, name text, grade int, PRIMARY KEY(ID)) ; create table Friend(ID1 INT, ID2 INT, FOREIGN KEY(ID1) REFERENCES Highschooler(ID), FOREIGN KEY(ID2) REFERENCES Highschooler(ID)) ;
The PRIMARY KEY
constraint states that a collection,
usually one, of attributes uniquely identifies each row of the tab.
The FOREIGN KEY
constraint is similar to a pointer
declaration. It states the an attribute “points to”
a row of another table. That is, that attribute’s value
is also an attribute
value within another table.
The UNIQUE
and NOT NULL
attributes
are self explanatory.
If a row is deleted, inserted, or updated so that a constraint is no longer satisfied, the database is supposed to indicate an exception and abort the operation.
Most SQL systems make an effort to create and maintain indexes
on
attributes used for keys.
Finally, some database professionals believe that these constraints should be supported in the middleware, rather than in SQL.
References from sqlite
Examples from Stanford database course
Many database managements systems to not
support CHECK
.
create table Student(sID int primary key, sName text, GPA real, sizeHS int); create table College(cName text, state text, enrollment int, primary key (cName,state)); create table Apply(sID int, cName text, major text, decision text, unique(sID,cName), unique(sID,major)); create table Student(sID int, sName text, GPA real check(GPA <= 4.0 and GPA > 0.0), sizeHS int check(sizeHS < 5000));
Views
SQL allows the creation of views that provide a way of looking at several joined tables as a single table.
CREATE VIEW HighschoolFriends(name1 TEXT, name2 TEXT) AS SELECT H1.name AS name1, H2.name AS name2 FROM Higschooler H1, Highschooler H2, Friend F WHERE H1.ID = F.ID1 AND H2.ID = F.ID2 and H1.name < H2.name ;
Views provide a way of denormalizing relations and of looking at selected rows of a database. Most “users” of the database, human and program, only access the database through views. Views also provide a means of protecting internal details of the databases, similar to how private variables hide implementation details in object-oriented programming.
Obviously, the updating of a view must be done very carefully.
References from sqlite
Examples from Stanford database course
create view CSaccept as select sID, cName from Apply where major = 'CS' and decision = 'Y'; create view CSberk as select Student.sID, sName, GPA from Student, CSaccept where Student.sID = CSaccept.sID and cName = 'Berkeley' and sizeHS > 500;
Triggers
Triggers are sequences of SQL commands that intercept databases updates and perform a programmer specified actions. For example, when a view is “updated,” a trigger may be called to update the many tables used in the view. They are difficult to program.
Triggers use the identifiers NEW
and OLD
to refer to the present and hoped-for values of updated attributes.
References from sqlite
Examples from Stanford database course
create trigger CSacceptDelete instead of delete on CSaccept for each row begin delete from Apply where sID = Old.sID and cName = Old.cName and major = 'CS' and decision = 'Y'; end;
Transactions
Database systems support a transaction mechanism to make sure that several related SQL commands are executed either completely or not at all. That is the query passes the ACID test: Atomic, Consistent, Isolated, and Durable.
The implementation of transactions varies widely in SQL database management systems. There are even variations in the syntax used to specify transactions.
Transactions is a topic for another lecture in which the transaction mechanisms of SQL will be compared to those of journaled based file system such as Windows NTFS and Linux ext4.
References from sqlite
Access privileges
Almost all database support the SQL GRANT
statement
which can be used to control access (SELECT
, INSERT
and UPDATE
) to relations and attributes of a database.
Because sqlite depends totally on the
file access control provided by the operating system, it
does not have GRANT
and REVOKE
commands.
Database system administrators usually execute the
GRANT
and REVOKE
commands.
Database developers stick with the other SQL commands.
References from MySQL
How your database was created
create database brockjdDB ; grant all on brockjdDB.* TO 'brockjd'@'%' ;