2016-11-12

How to create a table

If we want to create and modify data, we need to know two other sets of commands.

  • CREATE TABLE
  • DROP TABLE

While they are written as two words, they are actually single commands.

Create table: an example

The following statements create the four tables in our survey database:

CREATE TABLE Person(id text, personal text, family text);
CREATE TABLE Site(name text, lat real, long real);
CREATE TABLE Visited(id integer, site text, dated text);
CREATE TABLE Survey(taken integer, person text, quant real, reading real);

Delete table

We can get rid of one of our tables using:

DROP TABLE Survey;

Be very careful when doing this: most databases have some support for undoing changes, but it's better not to have to rely on it.

Data column Types

Different database systems support different data types for table columns, but most provide the following:

data type use
INTEGER a signed integer
REAL a floating point number
TEXT a character string
BLOB a "binary large object", such as an image

Creating table: in details

Specify several kinds of constraints on its columns.

  • For example, a better definition for the Survey table would be:
CREATE TABLE Survey(
    taken   integer not null, -- where reading taken
    person  text,             -- may not know who took it
    quant   real not null,    -- the quantity measured
    reading real not null,    -- the actual reading
    primary key(taken, quant),
    foreign key(taken) references Visited(id),
    foreign key(person) references Person(id)
);

What else can we do?

Once tables have been created, we can

  • Add: INSERT
  • Change: UPDATE
  • Remove: DELETE

records.

Add info: INSERT

The simplest form of INSERT statement lists values in order:

INSERT INTO Site values('DR-1', -49.85, -128.57);
INSERT INTO Site values('DR-3', -47.15, -126.72);
INSERT INTO Site values('MSK-4', -48.87, -123.40);

We can also insert values into one table directly from another:

CREATE TABLE JustLatLong(lat text, long text);
INSERT INTO JustLatLong SELECT lat, long FROM Site;

Change: UPDATE

Tell the database which table we want to update, what we want to change the values to for any or all of the fields, and under what conditions we should update the values.

  • For example, if we made a mistake when entering the lat and long values of the last INSERT statement above:
UPDATE Site SET lat=-47.87, long=-122.40 WHERE name='MSK-4';

Be careful to not forget the where clause or the update statement will modify all of the records in the database.

Remove in single table: DELETE

If all we care about is a single table, we can use the DELETE command with a WHERE clause that matches the records we want to discard.

  • For example, once we realize that Frank Danforth didn't take any measurements, we can remove him from the Person table like this:
DELETE FROM Person WHERE id = 'danforth';

Remove in multile table: trickier

But what if we removed Anderson Lake instead?

  • Our Survey table would still contain seven records of measurements he'd taken, but that's never supposed to happen:
    • Survey.person is a foreign key into the Person table, and all our queries assume there will be a row in the latter matching every value in the former.
  • We have to ensure that the database remains internally consistent.

Referential Integrity

This problem is called referential integrity:

  • We need to ensure that all references between tables can always be resolved correctly.
    • One way to do this is to delete all the records that use 'lake' as a foreign key before deleting the record that uses it as a primary key.
    • If our database manager supports it, we can automate this using cascading delete.
    • However, this technique is outside the scope of this chapter.