CREATE.sql - master - fess/BaseDate

CREATE.sql
52 строки2 KB
Станислав Константинов
upload files
02 фев 2026, 11:34
02 фев 2026, 11:3422536bb
-- 1. Таблица жанров
CREATE TABLE IF NOT EXISTS Genres (
    Id SERIAL PRIMARY KEY,
    Name VARCHAR(100) NOT NULL UNIQUE
);

-- 2. Таблица исполнителей (без прямого указания жанра)
CREATE TABLE IF NOT EXISTS Artists (
    Id SERIAL PRIMARY KEY,
    Name VARCHAR(200) NOT NULL UNIQUE
);

-- 3. Таблица альбомов (без прямого указания исполнителя)
CREATE TABLE IF NOT EXISTS Albums (
    Id SERIAL PRIMARY KEY,
    Title VARCHAR(200) NOT NULL,
    ReleaseYear INTEGER CHECK (ReleaseYear > 1900 AND ReleaseYear <= EXTRACT(YEAR FROM CURRENT_DATE))
);

-- 4. Таблица треков
CREATE TABLE IF NOT EXISTS Tracks (
    Id SERIAL PRIMARY KEY,
    Title VARCHAR(200) NOT NULL,
    Duration INTEGER NOT NULL CHECK (Duration > 0), -- в секундах
    AlbumId INTEGER NOT NULL REFERENCES Albums(Id) ON DELETE CASCADE
);

-- 5. Таблица сборников
CREATE TABLE IF NOT EXISTS Compilations (
    Id SERIAL PRIMARY KEY,
    Title VARCHAR(200) NOT NULL,
    ReleaseYear INTEGER CHECK (ReleaseYear > 1900 AND ReleaseYear <= EXTRACT(YEAR FROM CURRENT_DATE))
);

-- 6. Связь: исполнители и жанры
CREATE TABLE IF NOT EXISTS ArtistGenres (
    ArtistId INTEGER NOT NULL REFERENCES Artists(Id) ON DELETE CASCADE,
    GenreId INTEGER NOT NULL REFERENCES Genres(Id) ON DELETE CASCADE,
    PRIMARY KEY (ArtistId, GenreId)
);
-- 7. Связь: альбомы и исполнители
CREATE TABLE IF NOT EXISTS AlbumArtists (
    AlbumId INTEGER NOT NULL REFERENCES Albums(Id) ON DELETE CASCADE,
    ArtistId INTEGER NOT NULL REFERENCES Artists(Id) ON DELETE CASCADE,
    PRIMARY KEY (AlbumId, ArtistId)
);
-- 8. Связь: сборники и треки
CREATE TABLE IF NOT EXISTS CompilationTracks (
    CompilationId INTEGER NOT NULL REFERENCES Compilations(Id) ON DELETE CASCADE,
    TrackId INTEGER NOT NULL REFERENCES Tracks(Id) ON DELETE CASCADE,
    PRIMARY KEY (CompilationId, TrackId)
);