Как привязать ключи в таблицах баз данных?

Question

[David David]

Всем привет. Я тут изучаю PostreSQL и у меня завалялась задача с собеседования еще столетней давности, думаю вам знакома эта структура. так Вот мне захотелось с нуля создать базу и таблицы по этой задаче.



Есть компания, доставляющая заказы - Курьерская Служба.
1. Таблица order содержит информацию о заказе, который будет доставлять курьер.
FIO – ФИО получателя;
address – адрес доставки;
kurier – код сотрудника доставившего отправление, по умолчанию null;
price – стоимость доставки;
2. Таблица trace трекинг заказа, содержит историю различных статусов заказа.
state – код статуса заказа: 1 – новый; 2 – принят на склад; 3 – доставлен; 4 – не доставлен
datetime – дата и время простановки статуса;
3. Таблица kurier содержит информацию о сотрудниках (курьеры и менеджеры).
FIO – ФИО курьера
manager – у курьера содержит код менеджера, по умолчанию null

Я пока что просто хочу создать таблицы. вот мое решение:

courierservicedb=# CREATE TABLE "order" (
courierservicedb(# code SERIAL PRIMARY KEY,
courierservicedb(# FIO VARCHAR(255),
courierservicedb(# address VARCHAR(255),
courierservicedb(# kurier INT,
courierservicedb(# price DECIMAL(8, 2)
courierservicedb(# );
CREATE TABLE
courierservicedb=# INSERT INTO "order" (FIO, address, kurier, price)
courierservicedb-# VALUES ('Иванов Иван Иванович', 'Невский проспект, 11', NULL, '100.00'),
courierservicedb-# ('Иванова Анна Ивановна', 'Набережная реки Мойки, 48', NULL, '150.00');
INSERT 0 2
courierservicedb=# SELECT * FROM "order";
 code |          fio          |          address          | kurier | price
------+-----------------------+---------------------------+--------+--------
    1 | Иванов Иван Иванович  | Невский проспект, 11      |        | 100.00
    2 | Иванова Анна Ивановна | Набережная реки Мойки, 48 |        | 150.00
(2 rows)
courierservicedb=# CREATE TABLE trace (
courierservicedb(# code INTEGER PRIMARY KEY,
courierservicedb(# state INT,
courierservicedb(# "order" INTEGER REFERENCES "order"(code),
courierservicedb(# datetime TIMESTAMP
courierservicedb(# );
courierservicedb=# SELECT * FROM trace;
 code | state | order | datetime
------+-------+-------+----------
(0 rows)
courierservicedb=# INSERT INTO "trace" (state, "order", datetime)
courierservicedb-# VALUES ('1', '1', '2022-05-09 12:10:57'),
courierservicedb-# ('2', '2', '2022-05-04 12:10:57'),
courierservicedb-# ('3', '2', '2022-05-08 13:10:57'),
courierservicedb-# ('4', '1', '2022-05-09 14:20:57'),
courierservicedb-# ('2', '2', '2022-05-09 11:10:57');
ERROR:  null value in column "code" of relation "trace" violates not-null constraint
DETAIL:  Failing row contains (null, 1, 1, 2022-05-09 12:10:57).

Я остановился на второй таблице, не могу получается установить связь с первой таблице trace по ключам, я так понимаю из-за того что code один и тот же, или я что то не так делаю? и как бы вы создавали таблицы? И еще вопросы как заменить типы данных и правильно ли я заменил их? почему-то типы datetime, и TINYINT в простгресе ругается на синтаксические ошибки

Answer 1

[Rsa97]

Значение code необходимо задавать явно для каждой записи в trace или использовать тип SERIAL. Это поле не может быть null, так как является первичным ключом.
Ну и не заданы отношения FOREIGN KEY.

Comments

[David David]

Спасибо за отклик. Ты имеешь ввиду, что связь между двумя таблицами - поле code а не order? и в таблице trace первычный ключ - order? я правильно понимаю?

[Rsa97]

David It, Стоп, действительно. Тут я ошибся, это действительно order. Меня сбили с толку нетрадиционные названия полей. Обычно поле идентификатора называют id, а поле связи было бы order_id.
Поправил ответ.

[David David]

Rsa97, Вот так правильно будет таблицу создатвать?

CREATE TABLE trace (
code SERIAL PRIMARY KEY,
state INT,
FOREIGN KEY ("order") REFERENCES "order" (code)
datetime TIMESTAMP
);

[David David]

Rsa97, поля я добавлял по рисунку. не увидел id в них

[Rsa97]

David It, Да, как-то так.

[David David]

Rsa97, Вот так у меня получилось) спасибо. отметил решение правильным

courierservicedb=# CREATE TABLE trace (
courierservicedb(# code SERIAL PRIMARY KEY,
courierservicedb(# state INT,
courierservicedb(# "order" INT,
courierservicedb(# datetime TIMESTAMP,
courierservicedb(# FOREIGN KEY ("order")
courierservicedb(# REFERENCES "order" (code));
CREATE TABLE
courierservicedb=# INSERT INTO trace (state, "order", datetime)
courierservicedb-# VALUES ('1', '1', '2022-05-09 12:10:57'),
courierservicedb-# ('2', '2', '2022-05-04 12:10:57'),
courierservicedb-# ('3', '2', '2022-05-08 13:10:57'),
courierservicedb-# ('4', '1', '2022-05-09 14:20:57'),
courierservicedb-# ('2', '2', '2022-05-09 11:10:57');
INSERT 0 5
courierservicedb=# SELECT * FROM trace;
 code | state | order |      datetime
------+-------+-------+---------------------
    1 |     1 |     1 | 2022-05-09 12:10:57
    2 |     2 |     2 | 2022-05-04 12:10:57
    3 |     3 |     2 | 2022-05-08 13:10:57
    4 |     4 |     1 | 2022-05-09 14:20:57
    5 |     2 |     2 | 2022-05-09 11:10:57
(5 rows)

[Akina]

Rsa97,

Обычно поле идентификатора называют id, а поле связи было бы order_id.

Практика обычная - но плохая. Гораздо разумнее в обеих таблицах использовать имя поля order_id. В таблице order сразу понятно, что это её PK, а в любой другой - что это FK. А в любом запросе сразу видно, правильно ли построено условие связывания таблиц.

Answer 2

[Slava Rozhnev]

Я бы предложил следующую структуру таблиц:

CREATE TABLE employees (
	id SERIAL PRIMARY KEY,
	manager_id INT REFERENCES employees(id),
	full_name VARCHAR,
	address VARCHAR,
	phone VARCHAR,
	hired_at TIMESTAMP,
	fired_at TIMESTAMP
);

CREATE TABLE order_states (
	id SERIAL PRIMARY KEY,
	state VARCHAR
);

CREATE TABLE orders (
	id SERIAL PRIMARY KEY,
	customer VARCHAR,
	address VARCHAR,
	courier_id INT REFERENCES employees(id),
	price NUMERIC
);

CREATE TABLE trace (
	id BIGSERIAL PRIMARY KEY,
	state_id INT REFERENCES order_states(id),
	order_id INT REFERENCES orders(id),
	updated_at TIMESTAMP
);

CREATE INDEX idx_employees_manager_id ON employees(manager_id);

CREATE INDEX idx_orders_courier_id ON orders(courier_id);

CREATE INDEX idx_trace_state_id_order_id ON trace(state_id, order_id);

SQLize online

Comments

[David David]

Не плохо не плохо... А таблица employees это таблица курьера?

[Slava Rozhnev]

David It, курьеров, менеджеров и прочих сотрудников

[David David]

Slava Rozhnev, Еще хотел бы узнать для чего ты создал таблицу order_states?

[Slava Rozhnev]

David It, Это таблица - словарь. В ней будут храниться возможные статусы заказов: 1 - "новый", 2 - "в доставке", 3 - "доставлен", 4 - "отменен"

В самой таблице заказов я буду хранить только числовое значение статуса (1, 2, 3, 4)