Part4_SQL/Task_4_2_2/MusicDB.sql - master - ArtT/Netology

Part4_SQL/Task_4_2_2/MusicDB.sql
96 строк3 KB
ArtT
Добавил задание 4_2_2
25 апр 2026, 14:52
25 апр 2026, 14:52018d70e
CREATE TABLE IF NOT EXISTS "MusicStyle"
(
    "ID" integer NOT NULL GENERATED ALWAYS AS IDENTITY,
    "NAME" character varying(64) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT "MusicStyle_pkey" PRIMARY KEY ("ID")
);

CREATE TABLE IF NOT EXISTS "Performer"
(
    "ID" integer NOT NULL GENERATED ALWAYS AS IDENTITY,
    "Name" character varying(64) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT "Performer_pkey" PRIMARY KEY ("ID")
);

CREATE TABLE IF NOT EXISTS "MusicStylePerformer"
(
    "ID" integer NOT NULL GENERATED ALWAYS AS IDENTITY,
    "MusicStyleID" integer NOT NULL,
    "PerformerID" integer NOT NULL,
    CONSTRAINT "MusicStylePerformer_pkey" PRIMARY KEY ("ID"),
    CONSTRAINT "CHECK1_MusicStylePerformer" UNIQUE ("MusicStyleID", "PerformerID"),
    CONSTRAINT "FK1_MusicStylePerformer" FOREIGN KEY ("MusicStyleID")
        REFERENCES "MusicStyle" ("ID") MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT "FK2_MusicStylePerformer" FOREIGN KEY ("PerformerID")
        REFERENCES "Performer" ("ID") MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        NOT VALID
);


CREATE TABLE IF NOT EXISTS "Albom"
(
    "ID" integer NOT NULL GENERATED ALWAYS AS IDENTITY,
    "Name" character varying(32) COLLATE pg_catalog."default" NOT NULL,
    "DateCteate" date NOT NULL,
    CONSTRAINT "Albom_pkey" PRIMARY KEY ("ID")
);

CREATE TABLE IF NOT EXISTS "Track"
(
    "ID" integer NOT NULL GENERATED ALWAYS AS IDENTITY,
    "AlbomID" integer NOT NULL,
    "Name" character varying COLLATE pg_catalog."default",
    "fTime" interval second(0) NOT NULL,
    CONSTRAINT "Track_pkey" PRIMARY KEY ("ID"),
    CONSTRAINT "FK_TRACK_1" FOREIGN KEY ("AlbomID")
        REFERENCES "Albom" ("ID") MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
);

CREATE TABLE IF NOT EXISTS "PerformerAlbom"
(
    "ID" integer NOT NULL GENERATED ALWAYS AS IDENTITY,
    "PerformerID" integer NOT NULL,
    "AlbomID" integer NOT NULL,
    CONSTRAINT "PerformerAlbom_pkey" PRIMARY KEY ("ID"),
    CONSTRAINT "CHECK1_PerformerAlbom" UNIQUE ("PerformerID", "AlbomID"),
    CONSTRAINT "FK1_PerformerAlbom" FOREIGN KEY ("PerformerID")
        REFERENCES "Performer" ("ID") MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT "FK2_PerformerAlbom" FOREIGN KEY ("AlbomID")
        REFERENCES "Albom" ("ID") MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
);


CREATE TABLE IF NOT EXISTS "Collection"
(
    "ID" integer NOT NULL GENERATED ALWAYS AS IDENTITY,
    "Name" character varying COLLATE pg_catalog."default",
    "DateCreate" date NOT NULL,
    CONSTRAINT "Collection_pkey" PRIMARY KEY ("ID")
);


CREATE TABLE IF NOT EXISTS "CollectionTrack"
(
    "ID" integer NOT NULL GENERATED ALWAYS AS IDENTITY,
    "CollectionID" integer NOT NULL,
    "TrackID" integer NOT NULL,
    CONSTRAINT "CHECK1_CollectionTrack" UNIQUE ("CollectionID", "TrackID"),
    CONSTRAINT "FK1_CollectionTrack" FOREIGN KEY ("CollectionID")
        REFERENCES "Collection" ("ID") MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT "FK2_CollectionTrack" FOREIGN KEY ("TrackID")
        REFERENCES "Track" ("ID") MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
);