Заполнить две связанные таблицы SQLite

Question

Были созданы две таблицы:

таблица Persons

CREATE TABLE Persons
(
Id INTEGER PRIMARY KEY AUTOINCREMENT,
Firstname TEXT, 
Lastname TEXT,
Surname TEXT,
DateOfBirth TEXT
)

и таблица Employess

CREATE TABLE Employees
(
Id INTEGER PRIMARY KEY AUTOINCREMENT,
PersonId INTEGER NOT NULL, 
PersonnelNumber INTEGER NOT NULL,
PostId INTEGER NOT NULL,
FOREIGN KEY (PersonId) REFERENCES Persons (Id) ON DELETE CASCADE
)

Столбец PersonId является внешним ключом, который указывает на столбец Id из таблицы Persons.

Есть форма WindowsForms с текстбоксами, данными из которых нужно заполнить эти две таблицы. Проблема в том, что я не знаю, как заполнить одновременно Persons и Employees по Id из Persons. В первой таблице заполняются поля FirstName, Lastname, Surname и DateOfBirth.

 public void CreateEmployee()
 {
     using (var connection = new SqliteConnection("Data Source=mainDb.db"))
     {
         connection.Open();

         try
         {
             SqliteCommand command = new SqliteCommand();
             command.Connection = connection;
             command.CommandText = "INSERT INTO Persons (Firstname, Lastname, Surname, DateOfBirth) VALUES (@Firstname, @Lastname, @Surname, @DateOfBirth)";
             command.Parameters.AddWithValue("@Firstname", createEmployeeFirstNameInputBox.Text);
             command.Parameters.AddWithValue("@Lastname", createEmployeeLastNameInputBox.Text);
             command.Parameters.AddWithValue("@Surname", createEmployeeSurNameInputBox.Text);
             command.Parameters.AddWithValue("@DateOfBirth", createEmployeeDateOfBirthInputBox.Text);
             command.ExecuteNonQuery();
             MessageBox.Show("Сотрудник создан");

         }
         catch (Exception ex)
         {
             MessageBox.Show($"Не удалось создать сотрудника:\n\"{ex.Message}\"\n" +
                 $"Обратитесь к системному администратору для её устранения.",
                 "Нет соединения с Базой Данных", MessageBoxButtons.OK, MessageBoxIcon.Stop);
         }
         DataBase.CloseConnection();
     }
 }

В таблице Employees по данным из формы должны заполняться колонки PersonId, PersonnelNumber и PostId. Как правильно составить запрос, чтобы заполнялись обе таблицы одновременно по связанному Id?

Answer 1

В формате SQL запросы выглядят так:

INSERT INTO Persons (Firstname, Lastname, Surname, DateOfBirth) 
VALUES (@Firstname, @Lastname, @Surname, @DateOfBirth);

INSERT INTO Employees (PersonId, PersonnelNumber, PostId)
SELECT Persons.Id, @PersonnelNumber, @PostId
FROM Persons 
WHERE (Firstname, Lastname, Surname, DateOfBirth) = (@Firstname, @Lastname, @Surname, @DateOfBirth);

То есть при вставке записи во вторую таблицу мы получаем значение только что сгенерированного первичного ключа первой таблицы по значениям только что введённых в неё данных.

Однако сильно беспокоит отсутствие уникальных индексов в таблицах. Что запросто может привести к появлению полных дубликатов записей, различающихся только значением автоинкрементного первичного ключа. Следует заново проанализировать предметную область на предмет уникальности признаков либо их совокупности, внести изменения в диаграмму и на этой основе обновить/дополнить уникальными индексами структуры таблиц.

БЛОГ НА HUSL

Заполнить две связанные таблицы SQLite

Ответы (1 шт):