Composite primary key

CREATE TABLE with a composite primary key

schema:scott

A composite key has more than one attribute (field). In this example we store details of tracks on albums - we need to use three columns to get a unique key - each album may have more than one disk - each disk will have tracks numbered 1, 2, 3...

The primary key must be different for each row of the table. The primary key may not contain a null.

DROP TABLE track

CREATE TABLE track(
  album CHAR(10),
  disk INTEGER,
  posn INTEGER,
  song VARCHAR(255),
  PRIMARY KEY (album, disk, posn)
)

CREATE TABLE track(
  album CHAR(10) NOT NULL,
  dsk INTEGER NOT NULL,
  posn INTEGER NOT NULL,
  song VARCHAR(255),
  PRIMARY KEY (album, dsk, posn)
)

We must explicitly state that the components are NOT NULL.

CREATE and DROP Operations

CREATE a new table

DROP a table

Composite primary key

CREATE a foreign key

CREATE a VIEW

Autonumber fields

ALTER TABLE ... ADD COLUMN

ALTER TABLE ... DROP COLUMN

ALTER TABLE ... ADD constraint

CREATE TABLE problems: Invalid column name.

CREATE TABLE problems: Insufficient privileges.

CREATE TABLE problems: Table already exists.

DROP TABLE problems: Foreign key references.

CREATE TABLE problems: Foreign key references.

Rename column