1

I have the following scenario: A 'phone' child table can serve several parent tables through join tables, as follows:

enter image description here

CREATE TABLE phone (

    id BIGINT AUTO_INCREMENT,
    number VARCHAR(16) NOT NULL,
    type VARCHAR(16) NOT NULL,

    PRIMARY KEY(id)     

);

CREATE TABLE employee_phone (

    id BIGINT AUTO_INCREMENT,
    employee BIGINT NOT NULL,
    phone BIGINT NOT NULL,

    PRIMARY KEY(id),
    CONSTRAINT empl_phone_u_phone UNIQUE(phone),
    CONSTRAINT empl_phone_fk_employee
        FOREIGN KEY(employee)
        REFERENCES employee(id) ON DELETE CASCADE,
    CONSTRAINT empl_phone_fk_phone
        FOREIGN KEY(phone)
        REFERENCES phone(id) ON DELETE CASCADE

);

Let Alice and Bob live in the same house and be employees of the same company. HR has two phone numbers registered for Alice whereas they have only one for Bob, the number of the house's landline. Is there a way to enforce at database level that a phone number (number-type) cannot be repeated for the same employee (or supplier, or whatever parent appears later), using this configuration? Or will I have to take care of such restrictions in the application layer? I'd rather not use triggers or table denormalization (as seen in related questions on the site such as this one, which work with IDs, not with other fields), but I'm open to do so if there's no alternative. I'm using MySQL. Thanks for your attention.

Community
  • 1
  • 1
jpangamarca
  • 713
  • 2
  • 13
  • 33

1 Answers1

0

If I understand correctly, you just want unique constraints on the junction tables:

alter table employee_phone add constraint unq_employeephone_employee_phone unique (employee, phone);

This will prevent duplicates for a given employee or (with the equivalent constraint) supplier.

If you want all phone numbers to be unique in the phone table, then just put a unique constraint/index on phone:

alter table phone add constraint unq_phone_phone unique (phone);

(you might want to include the type as well).

If you try to add a duplicate phone, the code will return an error.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks. Sadly, duplicate phone numbers are a possible case and can't be enforced to be unique. That's why the unique constraint in the join table covers only the phone column (see my first comment in the question). – jpangamarca Apr 12 '17 at 16:50
  • @jpangamarca . . . The unique constraint in the `join` table on the employee and phone columns does exactly what you say you want to do: "to enforce at database level that a phone number (number-type) cannot be repeated for the same employee (or supplier, or whatever parent appears later)". – Gordon Linoff Apr 13 '17 at 00:56
  • I'm sorry, I'm not being clear enough. Yes, the constraint works like that for the IDs, but what I need, is to disallow two identical numbers (number-type, let's forget about the IDs) for the same employee but to be allowed to have the same number-type for another employee. I can't have any unique constraints in the phone table. Am I clear now? – jpangamarca Apr 13 '17 at 17:42