реализация UNIQUE для связанных таблиц в MySQL

ПОЛЬЗОВАТЕЛЬ - это ЧЕЛОВЕК, а у ЛИЦА есть КОМПАНИЯ - пользователь -> человек - один к одному, человек -> компания - многие к одному.

person_id - это FK в таблице USER. company_id - это FK в таблице PERSON.

ЛИЦО может не быть ПОЛЬЗОВАТЕЛЕМ, но ПОЛЬЗОВАТЕЛЬ всегда остается ЛИЦОМ.

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

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

alter table user add unique(used_id,person.company_id);

но это не работает.

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


mysql sql constraints data-modeling database-design
person cliveholloway    schedule 20.09.2009    source источник

Ответы (3)


arrow_upward
1
arrow_downward

Что ж, нет ничего простого, чтобы делать то, что вы хотите. Вероятно, вы можете усилить необходимое ограничение с помощью триггеров BEFORE INSERT и BEFORE UPDATE < / a> хотя. См. этот вопрос SO о возникновении ошибок MySQL, чтобы узнать, как справиться с созданием триггеры не работают.

person chaos    schedule 20.09.2009
comment
Триггеры выглядят неплохо, но я думаю, что закончу с DB SCM, прежде чем идти по этому маршруту (см. Последний вопрос, который я разместил здесь, хе-хе). - person cliveholloway; 21.09.2009

arrow_upward
1
arrow_downward

Есть ли другие атрибуты в вашей PERSON таблице? Причина, по которой я спрашиваю, заключается в том, что вы хотите реализовать типичную таблицу следствий:

USERS таблица:

  • user_id (pk)

Таблица USER_COMPANY_XREF (в девичестве ЛИЦО):

  • user_id (pk, fk)
  • company_id (pk, fk)
  • EFFECTIVE_DATE (не ноль)
  • EXPIRY_DATE (не нуль)

COMPANIES таблица:

  • company_id (pk)

Первичный ключ таблицы USER_COMPANY_XREF, являющийся составным ключом USERS.user_id и COMPANIES.company_id, позволит вам связать пользователя с более чем одной компанией, не дублируя данные в таблице USERS, и обеспечит ссылочную целостность.

person OMG Ponies    schedule 20.09.2009
comment
Каждый пользователь связан только с одной компанией, но два человека в разных компаниях могут иметь одно и то же имя пользователя. Это то, что я пытаюсь установить - я думаю, что объединение пользовательских данных в таблицу людей и установка имени пользователя на null для людей, которые не являются пользователями, будут работать, и, возможно, будет проще всего реализовать ... - person cliveholloway; 21.09.2009
comment
Вот почему имена пользователей не используются для обеспечения ссылочной целостности. Таблицы в моем ответе позволят вам иметь пользователя без ассоциированной компании. - person OMG Ponies; 21.09.2009

arrow_upward
1
arrow_downward

Вы можете определить ограничение UNIQUE в таблице Person:

CREATE TABLE Company (
 company_id SERIAL PRIMARY KEY
) ENGINE=InnoDB;

CREATE TABLE Person (
 person_id SERIAL PRIMARY KEY,
 company_id BIGINT UNSIGNED,
 UNIQUE KEY (person_id, company_id),
 FOREIGN KEY (company_id) REFERENCES Company (company_id)
) ENGINE=InnoDB;

CREATE TABLE User (
 person_id BIGINT UNSIGNED PRIMARY KEY,
 FOREIGN KEY (person_id) REFERENCES Person (person_id)
) ENGINE=InnoDB;

Но на самом деле вам не нужно ограничение уникальности даже в таблице Person, потому что person_id уже уникален сам по себе. Невозможно, чтобы данное person_id могло ссылаться на две компании.

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

Повторите свой комментарий:

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

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

Поэтому, если у вас нет многих других атрибутов, специфичных для пользователей, я бы объединил User с Person и добавил столбец «is_user». Или просто полагайтесь на то, что это неявно верно, что Person с ненулевым cryptpass по определению является пользователем.

Тогда ваша проблема с ограничениями между таблицами UNIQUE исчезнет.

person Bill Karwin    schedule 20.09.2009
comment
Это не решает проблему, позволяющую одному и тому же имени пользователя существовать в разных компаниях. Думаю, я мог бы объединить таблицы USER и PERSON и оставить user / cryptpass NULL для людей без имен пользователей. Это сработает, поскольку это отношения один на один, но я думаю, что мне нужно подумать об этом, прежде чем идти по этому пути. - person cliveholloway; 21.09.2009