Composite primary key

From SQLZOO
Revision as of 15:04, 12 July 2012 by Marek (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

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)
)
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense