I have a very simple Database consist of;

Person_id, City and is_manager columns. is_manager can have TRUE or FALSE bit (0,1).

There are some restrictions that I manage to complete, such as;

1. each city can have one or more than one person (unique person_id City)
2. each person can be either manager or not in a city. (unique person city is_manager)
3. each city can have only one manager but can have more than one non-managers.
4. not every person has to have a city, some might not be assigned to a city at all.

I managed to do first two constraints easily but I couldn't manage for the third condition. because each city can have one manager, but can have more than one non_manager positions.

I have some other methodology to solve it but it will be not 100% on server-side solution. I want to have 100% SQL Server side solution.

I think it can be done with trigger;

• if so, how? (when is_manager TRUE comes, search whole database for that if city has any manager before, if not accept data, otherwise don't accept)
• if not necessary, how with constraints?

CodePudding user response：

A sample DB

create table Person(
    id int primary key,
    cityId int,
    constraint UK1 unique(cityId, id)
);


create table City (
    id int primary key,
    managerId int,
    constraint FK1 foreign key(id, managerId) references Person(cityId, id)
);

Business process is first assign a person to a City then make him/her a manager of the City.

db<>fiddle including test data