CODEWITHSUNDEEP
mysqlsql
sjp
sjp

Reputation: 392

(My)SQL - is it possible to have a unique constraint in referenced table

Let's say I have a database of families, persons and dogs. Each family has 0 or more persons, each person has 0 or more dogs and no dogs in the same family may have the same name. Is it possible to declare a uniqueness constraint for dogs that "jumps" one table, i.e. something like

CREATE TABLE Family (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    PRIMARY KEY (ID)
);
CREATE TABLE Person (
    ID int NOT NULL,
    FamilyID int NOT NULL,
    FirstName varchar(255) NOT NULL,
    PRIMARY KEY (ID),
    FOREIGN KEY (FamilyID) REFERENCES (Family.ID)
);
CREATE TABLE Dog (
    ID int NOT NULL,
    PersonID int NOT NULL,
    Name varchar(255) NOT NULL,
    PRIMARY KEY (ID),
    FOREIGN KEY (PersonID) REFERENCES (Person.ID),
    UNIQUE (Name, Person.FamilyID)
);

? UNIQUE(Name, Person.FamilyID) obviously does not work, but it should make my intentions clear.

If not, (or perhaps even better, actually) is it possible to have something like

CREATE TABLE Family (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    PRIMARY KEY (ID)
);
CREATE TABLE Person (
    ID int NOT NULL,
    FamilyID int NOT NULL,
    FirstName varchar(255) NOT NULL,
    PRIMARY KEY (ID),
    FOREIGN KEY (FamilyID) REFERENCES (Family.ID)
);
CREATE TABLE Dog (
    ID int NOT NULL,
    FamilyID int NOT NULL,
    PersonID int NOT NULL,
    Name varchar(255) NOT NULL,
    PRIMARY KEY (ID),
    FOREIGN KEY (FamilyID) REFERENCES (Family.ID),
    FOREIGN KEY (PersonID) REFERENCES (Person.ID),
    UNIQUE (Name, FamilyID)
);

and enforce consistency between Dog.FamilyID and Dog.PersonID? That is, to ensure that the FamilyID is the same for the dog and the person?

Bonus points if a dog can belong to the entire family by , i.e. Dog.PersonID is nullable.

Upvotes: 0

Views: 510

Answers (2)

The Impaler
The Impaler

Reputation: 48810

No, you cannot create UNIQUE constraints that span multiple tables (well... maybe in the exotic case of an oracle bitmap join index, but that's a different story).

The typical solution is to use composite keys. That way the column from the first table will be present in the second and third table, and it would be easy to enforce the uniqueness you want.

For example:

CREATE TABLE Family (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    PRIMARY KEY (ID)
);

CREATE TABLE Person (
    ID int NOT NULL,
    FamilyID int NOT NULL,
    FirstName varchar(255) NOT NULL,
    PRIMARY KEY (ID),
    UNIQUE (ID, FamilyID), -- unique composite constraint
    FOREIGN KEY (FamilyID) REFERENCES Family (ID)
);

CREATE TABLE Dog (
    ID int NOT NULL,
    PersonID int NOT NULL,
    FamilyID int NOT NULL,
    Name varchar(255) NOT NULL,
    PRIMARY KEY (ID),
    FOREIGN KEY (PersonID, FamilyID) REFERENCES Person (ID, FamilyID),
    UNIQUE (Name, FamilyID)
);

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269963

Forget it. Just attach the dog to a person. Then look up the family from the person.

Trying to managing redundant relationships across tables is just fraught with problems. It is quite easy to get the family looking up the person.

In other words, remove familyId from the dogs table.

Upvotes: 0

Related Questions