avatar tianjara.net | blog icon Andrew Harvey's Blog

COMP3311 - Wk3-4 General Notes
10th April 2009

Constraint Checking

If you use the keyword CONSTRAINT, you also need to provide a name for the constraint. However, it is permitted to omit both the keyword CONSTRAINT and the constraint name. In other words constraint definitions can be either

CONSTRAINT constraint_name CHECK ( expression )

or just

CHECK ( expression )

With respect to the expression,

x = NULL --this is always false
x IS NULL --returns true if x is null, and false otherwise


Standard paradigm for accessing DB from app.code:

-- establish connection to DBMS
db = dbConnect("dbname=X user=Y passwd=Z");
query = "select a,b from R,S where ... ";
-- invoke query and get handle to result set
results = dbQuery(db, query);
-- for each tuple in result set
while (tuple = dbNext(results)) {
-- process next tuple
process(val(tuple,'a'), val(tuple,'b'));
} dbClose(results);


create view name as select ...

This makes a "virtual table" called name that you can use in your subsequent SQL queries, but the table will dissapear when the connection is closed (or at least this is when I think it dissapears).


pg_dump dbname > file

This will dump the whole database (in SQL format) to a file. Use -o to ommit the ownership data.

Enforcing Case

SQL is case insensitive, to enforce case use double quotes. eg. select name as "Foo" from bar;

Foreign Keys

Just because an attribute in a foreign key does not automatically imply that it is not null. It may be NULL. If  you want the attribute to never be NULL you must add NOT NULL.

Tags: comp3311.