Как добавить запись в таблицу, имеющую внешний ключ

[andrystepa]

База MySQL. Есть таблица отделов Units со столбцами: Unit_Id, Unit_Name. Unit_Id - Primary key. Далее есть таблица сотрудников Users со столбцами User_Id, User_name, Unit_Id. User_Id - Primary key а Unit_Id естественно внешний ключ. Таблица отделось Units заполнена, а таблицу сотрудников надо будет заполнять (или добавлять/удалять) начальникам отделов. Начальник отдела знает только название своего отдела а идентификатор, естественно не знает. Как составить запрос на вставку (или удаление) сотрудника, чтобы идентификатор отдела был взят из таблицы отделов по его названию?
И, кроме того, как все это сделать с помощью пакетной вставки - сотрудников много.

[Sla]

напиши select выборки всех сотрудников
напишешь - пойдем дальше.

[andrystepa]

Sla,
Для выборки всех сотрудников? Но их еще нет в базе! Впрочем, если надо, вот выборка всех имен сотрудников:
SELECT User_name FROM Users;

[andrystepa]

Я попытался сделать вставку следующим образом:
NSERT INTO Users (User_name, Unit_Id)  SELECT Units.Unit_Id AS Unit_Id FROM Units WHERE Units.unit_name = 'Отдел ССистем Управления' VALUES ('Иванов И.', Unit_Id);
Но не вышло - ошибка в синтаксисе

[Алексей++]

andrystepa, по моему "VALUES" тут вообще не надо, а после селекта должно стоять 2 поля, а не одно

[andrystepa]

А как без VALUES я укажу, какое имя я туда хочу вставить? Добавил в SELECT поле unit_name:
INSERT INTO Users (User_name, Unit_Id)  SELECT Units.Unit_Id AS Unit_Id, unit_name FROM Units WHERE Units.unit_name = 'Отдел Систем Управления' VALUES ('Иванов И..', Unit_Id);
Все равно получил ошибку:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'VALUES ('Иванов И.', Unit_Id)' at line 1

[Sla]

andrystepa,
а никто тебя и не просил заносить сотрудников в базу
тебя попросили сделать выбрку сотрудников

select user_name, unit_name
  from users us, units un
  where us.unit_id=un.unit_id

[andrystepa]

Sla,
Такой запрос не пройдет: Нету в таблице Users ни одной записи. Вы вообще-то не сказали, какие поля надо показать в выборке. Я сделал только для имен. Если нужно для имен и названий отделов - тогда Вы безусловно правы. Но если в таблице сотрудников есть хоть одна запись. А пытался заносить я еще раньше - просто в первом посте я забыл показать как. Вот и написал вдогонку.

[Sla]

1. Кто-то должен сначала тех ачальников/отв.сотрудников внести.
2. получить unit_id начальника/отв.сотрудника
3. insert

[RXL]

andrystepa, не надо путаницы терминов - это не внешний ключ (FOREIGN KEY), а простое поле таблицы. Внешние ключи поддерживаются в MySQL только для типа таблиц InnoDB.

Код: (SQL)

SELECT TABLE_NAME, ENGINE
FROM INFORMATION_SCHEME.TABLES
WHERE TABLE_SCHEMA = 'имя базы'
    AND TABLE_NAME IN ('units', 'users');

По теме:

1.
В таблице users столбец user_id делаешь AUTO_INCREMENT, а столбец unit_id должен иметь дефолтное значение 0.

Код: (SQL)

INSERT INTO users (user_name)
VALUES ('Иванов'), ('Петров'), ('Сидоров');

UPDATE users
SET unit_id = (SELECT unit_id FROM units WHERE unit_name = 'Манагеры')
WHERE unit_id = 0;

2.
Запрос с UNION

Код: (SQL)

INSERT INTO users (user_name, unit_id)
SELECT u.unit_id, s.name
FROM units u, (
    SELECT 'Иванов' name
    UNION SELECT 'Петров'
    UNION SELECT 'Сидоров'
  ) s
WHERE u.unit_name = 'Манагеры';

Оба варианта работают с версией 4.1 и выше.

[andrystepa]

Sla,
А как их внести то? Для них ведь тоже надо указать Unit_Id!
RXL,
Это именно FOREIGN KEY - и все таблицы InnoDb. Видимо надо было указать описание этих таблиц.

Код:

mysql> DESCRIBE Units;
+-----------+----------------------+------+-----+---------+----------------+
| Field     | Type                 | Null | Key | Default | Extra          |
+-----------+----------------------+------+-----+---------+----------------+
| Unit_Id   | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| unit_name | varchar(30)          | YES  | UNI | NULL    |                |
+-----------+----------------------+------+-----+---------+----------------+

Это Units;

Код:

mysql> DESCRIBE Users;
+-----------+----------------------+------+-----+---------+----------------+
| Field     | Type                 | Null | Key | Default | Extra          |
+-----------+----------------------+------+-----+---------+----------------+
| User_Id   | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| User_name | varchar(31)          | NO   | UNI |         |                |
| Unit_Id   | smallint(5) unsigned | YES  | MUL | NULL    |                |
+-----------+----------------------+------+-----+---------+----------------+

Однако первый пример - где сначала INSERT  а потом UPDATE попробовал. Первый запрос сработал:

Код:

mysql> INSERT INTO Users(User_name) VALUES( 'Иванов И.' );
Query OK, 1 row affected (0.00 sec)

А вот со вторым запросом проблемы:

Код:

mysql> UPDATE Users SET Unit_id = (SELECT Unit_id FROM Units WHERE unit_name = 'Отдел Систем Управления')  WHERE Unit_Id = 0;
Query OK, 0 rows affected (0.00 sec)

Нифига не добавилось. Может тут как-то влияет кодировка? Я использовал для названий отделов cp1251.

[Sla]

andrystepa, вооот!
второй запрос был на update потому он и не выполнился

1. Есть НАЧАЛЬНИК
2. Есть Админ
3. Админ Заносит НАЧАЛЬНИКА в базу, и привязывает его к ОТДЕЛУ
4. НАЧАЛЬНИК получает свой  ID, который привязывается к таблице users
5. Получаешь unit_id НАЧАЛЬНИКА
6. Вставляешь сотрудника с unit_id начальника.

[andrystepa]

Sla,
Так как его привязать к отделу-то? Я же об этом и задавал вопрос!! Какая разница, кого привязывать к отделу - начальника или просто сотрудника? Базе то все равно, кто это! В таблицах нет поля, отличающего начальника от подчиненного.
Второй запрос был на Update. А вот почему он не выполнился - это мне неясно. Это что же получается, все запросы на Update должны не выполняться?

[McZim]

andrystepa, покажи что сейчас показывает: SELECT * FROM Users;

[andrystepa]

McZim,
Показывает все как есть:

Код:

mysql> SELECT * FROM Users;
+---------+------------+---------+
| User_Id | User_name  | Unit_Id |
+---------+------------+---------+
|       1 | Иванов И. |    NULL |
+---------+------------+---------+
1 row in set (0.00 sec)

[Sla]

UPDATE Users SET Unit_id=NNNN where uder_id=1

[andrystepa]

Sla,
Понятно. Только все это весьма коряво. Я же буду вставлять запрос на добавление сотрудника в PHP код. Получится что у меня в коде будет запрос, ссылающийся на unit_Id начальника отдела. А если он уволится - код надо переписывать? А я в отпуск уйду - и программа не будет работать, пока я в отпуске гуляю!

[Sla]

>>а таблицу сотрудников надо будет заполнять (или добавлять/удалять) начальникам отделов.
чьи слова?
На самом деле все намного проще
Должна работать система прав
НАЧАЛЬНИК - запись только в свой отдел
ОТВСОТРУДНИК отдела - запись в свой отдел
ОТВСОТРУДНИК офиса - запись в любой отдел

[andrystepa]

Так начальника могут и поменять! Или сам поменяется. Делать запрос на смену фамилии  - конечно можно, да только весь шлейф информации по рабочему времени предыдущего начальника потянется за ним. А это - задачи выполненные по различным проектам. Все это находится в другой таблице. То есть надо не только менять фамилию, но и удалять все записи относительно этой фамилии в таблице выполняемых задач. Не проще сразу сделать все прямо?

[andrystepa]

А система прав - это уже отдельный разговор. Она естественно будет! Но сначала надо хоть с добавлением сотрудников разобраться.

[Sla]

Сумбурная постановка задачи..

Начальник может поменяться....
Часто?
Или сам поменяться
Часто?
Сотрудник привязывается к unit_id отдела, а не к unit_id начальника
Смена unit_id начальника не приведет к смене unit_id сотрудника.

[andrystepa]

Sla,
Ну не такая уж сумбурная. В последние 4 месяца начальники отделов менялись 6 раз. Что сделаешь - кризис. Unit_Id сотрудника конечно не поменяется, но вот нового сотрудника - например того-же нового начальника - вводить уже придется вручную - пока не будет переделан код под нового начальника. А новых сотрудников у нас через отделы проходит много - студенты на практику постоянно приходят, каждый семестр.
Но все-же не в этом дело. Неужели для добавления в таблицу с внешним ключем новой записи невозможно получить этот самый ключ из связанной таблицы? Ведь такое должно быть необходимо в большинстве случаев - ведь разбивка на несколько связанных таблиц необходима для нормализации данных!

[andrystepa]

RXL,
Кстати, даже такой запрос:

Код:

UPDATE Users SET Unit_Id = 2 WHERE Unit_Id = 0;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

Как видно не срабатывает! Хотя запись с таким Unit_Id есть! Не понимаю, почему? Такой запрос приведен даже в книге Кузнецова, Симдянова "MySQL 5 в подлиннике".
Почему ?

[andrystepa]

Урра, кажется заработало! Немножко поменял второй запрос:

Код:

mysql> UPDATE Users, Units SET Users.Unit_Id = Units.Unit_Id WHERE Units.Unit_name = 'Отдел Систем Управления';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM Users;
+---------+------------+---------+
| User_Id | User_name  | Unit_Id |
+---------+------------+---------+
|       1 | Иванов И. |       2 |
+---------+------------+---------+
1 row in set (0.00 sec)

И таки сработала, зараза! Да вот только этот запрос добавит всем записям в таблице Users идентификатор одного отдела. Надо что-то еще добавлять.

[RXL]

Нифига не добавилось. Может тут как-то влияет кодировка? Я использовал для названий отделов cp1251.

Дык! Ты понимаешь, что ноль и NULL - разные значения? Первое - числовое значение, второе - отсутствие значения.

Либо ставь unit_id smallint(5) unsigned NOT NULL DEFAULT 0, либо в запросе поменяй условие: WHERE unit_id IS NULL.

Но я рекомендовал бы использовать второй вариант запроса - с UNION. Ведь если два начальника одновременно нажмут одинаковые кнопки, то может произойти нехорошая вещь.


А пост выше -- полный бред!!! Это у тебя сейчас одна запись в users, а будет две - обновятся обе.

[andrystepa]

RXL,
Спасибо большое! Теперь все ясно. Кстати, и то, что я написал выше вовсе не бред, если в запрос добавить еще одно условие:

Код:

UPDATE Users, Units SET Users.Unit_Id = Units.Unit_Id WHERE Units.Unit_name = 'Отдел Силовых Систем' AND Users.Unit_Id IS NULL;

Вот так тоже все работает без проблем.
А насчет второго варианта - конечно хорошо, если добавление идет одним запросом. Но вот только я не могу толком понять, что делается в этом запросе.
Начало понятно:

Код:

INSERT INTO users (user_name, unit_id)

Начинаем добавлять запись в поля user_name и unit_id. Далее должны идти сами данные, которые добавляются. Эти данные получаем из запроса:

Код:

SELECT u.unit_id, s.name
FROM units u, (
    SELECT 'Иванов' name
    UNION SELECT 'Петров'
    UNION SELECT 'Сидоров'
  ) s
WHERE u.unit_name = 'Манагеры';

Если я правильно понял u.unit_id, s.name это Units.Unit_Id и s.name где s создается вложенным запросом. А вот этот вложенный запрос я не понял. Какую функцию выполняет оператор SELECT в скобках? Он выбирает имена из чего? Далее - раз это правильно, зачем переменной s вы даете три значения? Разве при этом вставка повторится три раза? Я таких хитростей ни в одной книжке не встречал - поэтому сильно удивлен.

[RXL]

Один SELECT "строка" возвращает ровно одну строку. UNION позволяет объединить результаты двух SELECT с одинаковым форматом в один результат.

[Sla]

Один SELECT "строка" возвращает ровно одну строку. UNION позволяет объединить результаты двух SELECT с одинаковым форматом в один результат.

Один SELECT "строка" возвращает ровно одну строку. UNION позволяет объединить результаты нескольких  SELECT с одинаковым ФОМАТОМ в один результат.

[Sla]

конструкция
FROM units u, (
    SELECT 'Иванов' name
    UNION SELECT 'Петров'
    UNION SELECT 'Сидоров'
  ) s

означает что выборка будет делаться из таблицы units  и выделенной с алиасом s

[HandKot]

вмешаюсь в разговор
конструкцию вида

Код:

INSERT INTO users (user_name)
VALUES ('Иванов'), ('Петров'), ('Сидоров');

UPDATE users
SET unit_id = (SELECT unit_id FROM units WHERE unit_name = 'Манагеры')
WHERE unit_id = 0;

в данном случае использовать нельзя по одной простой причине, что если вводятся сотрудники сразу двух отделов, то какой отдел проставится сотрудникам ?
либо при вставке сначала вставлять некий уникальный unit_id и потом использовать его в where для отбора вставленных им и только им записей

не знаю как в MySQL (позволительна ли такая конструкция или нет), но в MSSQL я бы сделал так

Код:

INSERT INTO users (user_name, unit_id)
select
  'Иванов',
  unit_id
from
  units
where units_name = 'some unit'

что в принципе и предлагал RXL в самом начале