38 lines
1.1 KiB
SQL
38 lines
1.1 KiB
SQL
-- +goose Up
|
|
-- +goose StatementBegin
|
|
ALTER TABLE public.anglers ADD PRIMARY KEY (angler_id);
|
|
|
|
CREATE TABLE public.fish (
|
|
fish_id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
|
|
fish_name TEXT NOT NULL,
|
|
fish_desc TEXT NOT NULL,
|
|
fish_size_oz_max NUMERIC NOT NULL,
|
|
fish_size_oz_min NUMERIC NOT NULL
|
|
);
|
|
|
|
CREATE TABLE public.catches (
|
|
catch_id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
|
|
catch_caught_at TIMESTAMPTZ NOT NULL,
|
|
catch_size_oz NUMERIC NOT NULL,
|
|
angler_id UUID NOT NULL REFERENCES public.anglers(angler_id),
|
|
fish_id UUID NOT NULL REFERENCES public.fish(fish_id)
|
|
);
|
|
|
|
CREATE TABLE public.casts (
|
|
cast_id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
|
|
cast_started_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
cast_ended_at TIMESTAMPTZ,
|
|
angler_id UUID NOT NULL REFERENCES public.anglers(angler_id),
|
|
catch_id UUID REFERENCES public.catches(catch_id)
|
|
);
|
|
-- +goose StatementEnd
|
|
|
|
-- +goose Down
|
|
-- +goose StatementBegin
|
|
ALTER TABLE public.anglers DROP CONSTRAINT anglers_pkey
|
|
|
|
DROP TABLE public.fish;
|
|
DROP TABLE public.catches;
|
|
DROP TABLE public.casts;
|
|
-- +goose StatementEnd
|