Patrick Skelton Ответов: 1

Как в SQL обрабатывать отсутствующие значения внешнего ключа при добавлении новых записей?

Пожалуйста, простите общий характер этого вопроса, но я изо всех сил пытаюсь найти хороший совет о том, как справиться с этой проблемой.

Предположим, у меня есть две таблицы SQL, следующие:

Сотрудники: id, имя, department_id
1, "Джон", 1
2, "Мария", 3
3, "Кэти", 2
4, "Карен", 1

Отделы: id, имя
1, "продажи"
2, "администратор"
3, "цех"

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

Моя проблема возникает при попытке написать SQL для извлечения всех уникальных сотрудников без каких-либо дубликатов.

Если я просто использую...

SELECT e.id, e.name, d.department FROM Employees e INNER JOIN Departments d ON e.department_id = d.id;


..тогда я не получаю никого из новых сотрудников (то есть тех, кто еще не был назначен в отдел). Это означает, что пользователь приложения не может получить доступ к этим новым сотрудникам и поместить их в отдел.

Если с другой стороны, я использую...

SELECT e.id, e.name, d.department FROM Employees e INNER JOIN Departments d ON e.department_id = d.id OR e.department_id = -1;


..Я получаю много дубликатов записей, потому что каждый сотрудник без отдела включается в результат один раз для каждого отдела.

Я был бы очень признателен за несколько советов о том, как лучше всего справиться с этим.

Что я уже пробовал:

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

1 Ответов

Рейтинг:
10

Richard Deeming

Изменить department_id столбец в таблице Employees должен быть обнулен и использовать NULL чтобы указать, что отдел не был назначен. Это позволит вам создать ссылку на внешний ключ между двумя таблицами.

ALTER TABLE Employees
ALTER COLUMN department_id int NULL;
GO
UPDATE Employees
SET department_id = NULL
WHERE department_id = -1;
GO
ALTER TABLE Employees WITH CHECK
ADD CONSTRAINT FK_Employees_Department
FOREIGN KEY (department_id)
REFERENCES Departments (id);
GO
ALTER TABLE Employees
CHECK CONSTRAINT FK_Employees_Department;
GO


Если вы хотите получить записи, используйте LEFT JOIN:
SELECT 
    e.id, 
    e.name, 
    d.department
FROM
    Employees As e
    LEFT JOIN Departments As d
    ON d.id = e.department_id
;

Записи о сотрудниках, которым не назначены департамент вернется NULL для department колонка.

Визуальное представление SQL-соединений[^]

Patrick Skelton

Большое Вам за это спасибо. Я очень хорошо изучаю SQL, и мне потребовалось бы некоторое время, чтобы вспомнить раздел, который я прочитал в своей книге "левое соединение".

Я должен был упомянуть, что использую SQLite. Согласно документам, внешние ключи SQLite могут быть null по умолчанию, если только вы не добавите NOT NULL в определение столбца,так что мои таблицы должны быть в порядке, как они есть.

Моя проблема сейчас заключается в том, что я не могу понять, как заставить DBNull быть принятым в качестве параметра из C# в SQLiteCommand. Различные слепки на DBNull.Ценности не работают.

Я буду продолжать попытки и отправлю ответ, если найду что-то, что сработает.

Patrick Skelton

Ладно, это, может быть, и не слишком элегантно, но, похоже, работает. Спасибо еще раз.

свойство sqlcommand.Параметры.AddWithValue( "FK_Contact", (ContactID == -1 ) ? (object)DBNull.Значение: ContactID );

Patrick Skelton

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