Script_database/Новый скрипт бд.sql

--drop table MedicalHistory;
--drop table Animals;
--drop table Doctors;
--drop table Services;
--drop table Owners;
--drop table Users;

create table Users
(user_id serial not null constraint pk_user primary key,
Surname varchar(100) not null,
Name varchar(100) not null,
Phone varchar(20) not null constraint un_user_phone unique,
Email varchar(500) not null constraint un_user_email unique,
Password varchar(500) not null);

create table Doctors
(doctor_id serial not null constraint pk_doctor primary key,
Surname varchar(100) not null,
Name varchar(100) not null,
HireDate date not null,
Profession varchar(150) not null,
Honors text);

create table Services
(service_id serial not null constraint pk_service primary key,
Title varchar(100) not null,
Description text not null,
Price double precision not null);

create table ServiceDoctor
(service_id int not null,
doctor_id int not null,
constraint fk_service_to_servicedoctor foreign key (service_id)
references Services(service_id) on delete cascade,
constraint fk_doctor_to_servicedoctor foreign key (doctor_id)
references Doctors(doctor_id) on delete cascade)

create table Animals
(animal_id serial not null constraint pk_animal primary key,
Name varchar(100) not null,
Age int not null,
Breed varchar(100) not null constraint df_animal_breed default 'Отстутсвует',
Weight int not null,
Description text,
Owner int,
Image text,
constraint fk_owner_to_animal foreign key (Owner)
references Users(user_id) on delete cascade);

create table MedicalHistory
(kard_id serial not null constraint pk_kard primary key,
Animal int not null,
VisitingTime timestamp not null,
Doctor int not null,
Service int not null,
constraint fk_animal_to_kard foreign key (Animal)
references Animals(animal_id) on delete cascade,
constraint fk_doctor_to_kard foreign key (Doctor)
references Doctors(doctor_id) on delete cascade,
constraint fk_service_to_kard foreign key (Service)
references Services(service_id) on delete cascade);