Megan Taylor

front-end dev, volunteacher, news & data junkie, bibliophile, Flyers fan, sci-fi geek and kitteh servant

Intro to Databases: Constraints and Triggers, Constraints of several types

Non-null
Key
Attribute-based
Tuple-based
General assertions

College(cName, state, enrollment)
Student(sID, sName, GPA, sizeHS)
Apply(sID, cName, major, decision)

create table Student(sID int, sName text, GPA real not null, sizeHS int);

create table Student(sID int primary key, sName text, GPA real, sizeHS int);

create table Student(sID int primary key, sName text unique, 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)); create table Apply(sID int, cName text, major text, decision text, check(decision = 'N' or cName <> ‘Stanford’ or major <> ‘CS’));

create table Student(sID int, sName text, GPA real check(GPA is not null), sizeHS int);

create table T(A int check(A not in (select A from T)));
error – declare a table and refer to it before its been declared, sub check in check constraint

create table T(A int check((select count(distinct A) from T) = (select count(*) from T)));
error – declare a table and refer to it before its been declared
no subqueries or aggregation in check constraints

November 28, 2011 | Comments Off on Intro to Databases: Constraints and Triggers, Constraints of several types | Categories: Posts | Permalink

Comments are closed.

%d bloggers like this: