createSchema.sql 898 Bytes
Newer Older
1 2 3 4 5 6 7 8
CREATE TABLE IF NOT EXISTS owner
(
  id         BIGINT AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(255) NOT NULL,
  created_at DATETIME     NOT NULL,
  updated_at DATETIME     NOT NULL
);

9 10 11
-- Create enum for the race
CREATE TYPE IF NOT EXISTS RACE AS ENUM('ARABIAN', 'MORGAN', 'PAINT', 'APPALOOSA');

12 13 14 15 16 17 18
CREATE TABLE IF NOT EXISTS horse
(
  id          BIGINT AUTO_INCREMENT PRIMARY KEY,
  name        VARCHAR(255) NOT NULL,
  description TEXT,
  score       TINYINT      NOT NULL CHECK(score >= 1 AND score <= 5),
  birthday    DATE         NOT NULL,
19
  race        RACE         NOT NULL,
20
  image_path  VARCHAR(255) NOT NULL,
21 22 23 24
  created_at  DATETIME     NOT NULL,
  updated_at  DATETIME     NOT NULL
);

25
-- Add nullable owner foreign key
26 27
ALTER TABLE horse ADD IF NOT EXISTS owner_id BIGINT NULL;
ALTER TABLE horse ADD CONSTRAINT IF NOT EXISTS FL_OWNER FOREIGN KEY (owner_id) REFERENCES owner(id);