Допустим, у нас есть три таблицы Customer, City и Country.
Таблица страны:
Таблица города:
Таблица клиентов:
Как вы можете догадаться, Заказчик мог назначить или не назначить CityID или CountryID.
Итак, как лучше всего гарантировать, что при вставке/обновлении клиента мы не получим город, который не находится в указанной стране?
Я не уверен, что это будет лучший способ, но вы можете добавить функцию и использовать ее в своем проверочном ограничении:
CREATE FUNCTION CheckCityInCountry(@CityID int, @CountryID int)
RETURNS int
AS
BEGIN
DECLARE @retval int
SELECT @retval = COUNT(*)
FROM Cityies CI inner join Countries CO on (CI.CountryID, CO.ID)
WHERE CO.CountryID = @CountryID and CityID = @CityID
RETURN @retval
END;
GO
Эта функция вернет 1, если город находится в стране, и 0 в противном случае.
Затем добавьте контрольное ограничение, используя эту функцию:
ALTER TABLE Customers
ADD CONSTRAINT chk_CheckCityInCountry CHECK (
CityID is null OR
dbo.CheckCityInCountry(CityID,CountryID) >= 1
);
GO
Настройте отношения FK для таблиц Country и City.
Измените таблицу City, чтобы она содержала составной PK из CountryID и CityID.
Это отношение FK должно ссылаться на оба.
FK не применяется к нулевому значению.
Одно предостережение: вы можете ввести неправильный CityID с нулевой страной, так как в этот момент ни FK не применяется.
Но я думаю, что это можно было бы применить с помощью CHECK CONSTRAINT.
ALTER TABLE dbo.CustomerCountyCity ADD CONSTRAINT CK_CustomerCountyCity
CHECK (CountryID is not null OR CityID is Null)
