\begin{slide} \stitle{Check Constraints} \begin{slist} \item
Sometimes, constraints must be specified using general SQL
expressions.  \item Check constraints allow this.  \item Most
databases require the expression reference only the current row and
not reference run-time state like the system date.

\item Example:
\begin{alltt}
constraint circ_valid check( circ in(0,1))
\end{alltt}

					 \end{slist} \end{slide}
\begin{slide} \stitle{Check Example} 
\begin{alltt}
create table books (
  bookid      integer
    constraint bookid_primary primary key,
  title       varchar(255) not null, 
    --should really be table like authors
  series      varchar(255), 
    -- should really be table like authors
  position    integer,  -- where in series
  checkouts   integer not null  ,
  \underline{constraint position_meaningful check(position is null or position >= 0)}
);

create table authors (
  bookid      integer
  constraint authors_bookid_exists
    foreign key(bookid) references books(bookid),
  author      varchar(255),
  constraint authors_primary primary key (bookid, author)
);
\end{alltt}

\end{slide}

\begin{slide} \stitle{Default
Values} 
\begin{slist} 
\item A default clause can be added to columns
to specify a default value.  
\item Because of an Oracle bug, do not
include comments after default clauses; doing so can corrupt database
backups.  They are OK after the comma that closes the field or the
paren that closes the create table, but not before.  

\item Example:
\begin{salltt}
checkouts integer not null default 0
\end{salltt}
	\end{slist}

				     \end{slide}
\begin{slide}
\stitle{Default Example}
\begin{alltt}
create table books (
  bookid      integer
    constraint bookid_primary primary key,
  title       varchar(255) not null, 
    --should really be table like authors
  series      varchar(255), 
    -- should really be table like authors
  position    integer,  -- where in series
  checkouts   integer not null  \underline{default 0},
  constraint position_meaningful check(position is null or position >= 0)
);

create table authors (
  bookid      integer
  constraint authors_bookid_exists
    foreign key(bookid) references books(bookid),
  author      varchar(255),
  constraint authors_primary primary key (bookid, author)
);
\end{alltt}
\end{slide}