Composite primary key

From SQLZoo
Jump to navigation Jump to 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)
)
DataWars, Data Science Practice Projects - LogoDataWars: Practice Data Science/Analysis with +100 Real Life Projects