SQLize Online / PHPize Online  /  SQLtest Online

create table Client( customer_id number primary key not null, last_name varchar2(200) not null, first_name varchar2(100) not null, patronymic varchar2(100) not null, date_of_birth date, telephone_number varchar2(100) not null, email varchar2(100) not null, ); create table vehicle_characteristic ( --характеристики ТС vehicle_id number primary key not null, model varchar2(100) not null, year_of_issue varchar2(4) not null, --логичнее использовать чар/намбер mileage varchar(30) not null, --пробег initial_cost number not null ); create table salon_address ( address_id number primary key not null, country varchar2(100) not null, city varchar2(100) not null, street varchar2(100) not null, house varchar2(20), --может не быть чего-то, например мой адрес structure varchar2(20), building varchar2(20) ); create table salon ( salon_id number primary key not null, name varchar2(200) not null, working_hours varchar2(200) not null, telephone_number varchar2(100) not null, address_id number, --foreign key constraint Salon_Salon_Address foreign key (address_id) references salon_address(address_id) ); create table employee( employee_id number primary key not null, last_name varchar2(200) not null, first_name varchar2(100) not null, patronymic varchar2(100) not null, telephone_number varchar2(100) not null, salon_id number, --foreign key constraint Employee_Salon foreign key (salon_id) references salon(salon_id) ); create table Purchase_agreement( agreement_id number primary key not null, date_of_purchase date, customer number not null, --foreign key constraint Purchase_agreement_Client foreign key (customer) references client(customer_id), seller number not null, --foreign key constraint Purchase_agreement_Employee foreign key (seller) references Employee(employee_id) ); create table car( subject_of_agreement number primary key not null, agreement_id number not null, --foreign key constraint Car_Purchase_agreement foreign key (agreement_id) references Purchase_agreement(agreement_id), vehicle_id number not null, --foreign key constraint Car_Vehicle_Characteristic foreign key (vehicle_id) references vehicle_characteristic(vehicle_id), amount number not null, cost_per_piece number not null ); --доп 1 CREATE TABLE Service ( service_id NUMBER PRIMARY KEY NOT NULL, service_name VARCHAR2(200) NOT NULL, service_cost NUMBER NOT NULL CHECK (service_cost >= 0), -- Услуга не может иметь отрицательную стоимость duration_in_minutes NUMBER NOT NULL CHECK (duration_in_minutes > 0), -- Продолжительность услуги должна быть положительной salon_id NUMBER, -- foreign key CONSTRAINT Service_Salon FOREIGN KEY (salon_id) REFERENCES salon(salon_id), CONSTRAINT Service_Name_Unique UNIQUE (service_name) -- Услуга должна иметь уникальное имя ); --доп 2 -- 1. Удаляем внешний ключ из таблицы Purchase_agreement ALTER TABLE Purchase_agreement DROP CONSTRAINT Purchase_agreement_Client; -- 2. Изменяем тип данных первичного ключа customer_id в таблице Client ALTER TABLE Client MODIFY customer_id VARCHAR2(50); -- 3. Восстанавливаем внешний ключ в таблице Purchase_agreement ALTER TABLE Purchase_agreement ADD CONSTRAINT Purchase_agreement_Client FOREIGN KEY (customer) REFERENCES Client(customer_id);