NULLs in a composite primary key - SQL Server

Question

I'm trying to work out the best way of creating SQL Server primary keys, foreign keys and constraints to accurately represent my data model in LINQ / Entity Data Objects. Let's assume - for the purposes of simplification - that I have four main tables - Cats, Dogs, Pets and Owners - and one association table: OwnersToPets:

create table Cats (
    ID_Cats int NOT NULL IDENTITY PRIMARY KEY,
    Name nvarchar(50) NOT NULL,
    miceEaten int NULL
)

create table Dogs (
    ID_Dogs int NOT NULL IDENTITY PRIMARY KEY,
    Name nvarchar(50) NOT NULL,
    favouriteToy nvarchar(50) NULL
)

create table Owners (
    ID_Owners int NOT NULL IDENTITY PRIMARY KEY,
    Name nvarchar(50) NOT NULL,
)

create table Pets (
    ID_Pets int NOT NULL IDENTITY PRIMARY KEY,
    ID_Cats int references Cats(ID_Cats) NULL,
    ID_Dogs int references Dogs(ID_Dogs) NULL
)

create table OwnersToPets (
    ID_Owners int references Owners(ID_Owners) NOT NULL,
    ID_Pets int references Pets(ID_Pets) NOT NULL
)

When I create my entity objects by importing from the database, I get the following:

The problem is the relationship between Pets and Cats / Dogs. Each Cat / Dog has a single Pet reference. Each Pet object should have one Cat reference and one Dog reference, one of which will always be null. Conceptually, at least, there is a 1-to-0..1 between Cats / Dogs and Pets.

However, the entity data model created 0..1-to-many relationship between Cats / Dogs and Pets.

Any suggestions? Obviously, my database already exists and is currently in use with version 1 of the application I'm writing version 2 of, so I'd prefer not to have to change the database too much.

Answer 1

There is a lot of info missing in your question

• Can a pet have multiple owners? eg we have a pet, not I have a pet
• Do you expect more pet types (eg Rabbit, Bird, Fish)?

The problem with your current model (as shown) is the Pets table: it adds no value currently. Of course, your simplified design now hides information that probably makes it useful...

I'll throw in some ideas with assumptions though.

Note: This is correct design, whether EF can deal with it or not. That is, you design the model, implement the database, then make your client work with it

Option 1:

Assuming an owner can have 0..n dogs and/or 0..n pets, then you'd need 3 object tables and 2 link tables. You don't need Pet.

• Owner, Cat, Dog
• Link tables for CatOwner and DogOwner

This allows multiple owners per Dog and Cat. You can of course just have an OwnerID in Dog and Cat to restrict to one owner.

You can have different attributes for Cat and Dog here.

As you add more types, this becomes unwieldy (no matter how you model owners)

Option 2:

Do you need to distinguish Cat or Dog as separate entities? Isn't this just a type attribute of Pet?

**Pet
PetID, PK,
PetType, FK,
PetName
OwnerID, FK (optional, see below)
...

You can have a link table PetOwner (multiple owner) or OwnerID as an FK in Pet (single owner).

This requires each Cat or Dog (or Rabbit, Snake etc) object to have the same attributes.

Option 3:

The final way is superkey/subtype.

This is is useful because if combines elements of options 1 (different attributes per Cat and Dog) and option 2 (keeps the idea of Pet)

This defines one Pet object that has exactly one sub type of Dog, Cat, Rabbit, Snake etc. The super key comes from the extra UQ1 in Pet to enforce subtype. Pet as a table also stores common attributes.

**Pet
PetID, PK, UQ1
PetType, UQ1, 'Dog' or 'Cat'
PetName
OwnerID, FK (optional, see below)
...

**Dog
PetID, PK, FK1
PetType, PK, FK1, constraint = 'Dog'
Breed  -- per Dog,not per Pet
CatsChased
...

**Cat
PetID, PK, FK1
PetType, PK, FK1, constraint = 'Cat'
Breed -- per Cat, not per Pet
MiceEaten
...

Note: UQ1 is a unique constraint used by the FKs in Cat and Dog. It is a "super key" on Pet that allows sub-types via the FK

Again, you can have a link table PetOwner (multiple owner) or OwnerID as an FK in Pet (single owner).

Note, PetType can be a constant derived column in Dog and Cat

Edit: added tables for Pet, Cat and Dog.
I assume Owner is obvious...

CREATE TABLE PetSpecies (
    PetSpeciesID tinyint NOT NULL IDENTITY (1,1),
    Species varchar(100) NOT NULL,

    CONSTRAINT PK_PetSpecies PRIMARY KEY CLUSTERED (PetSpeciesID),
    CONSTRAINT UQ_PetSpecies UNIQUE (Species)
);
INSERT PetSpecies (Species) VALUES ('Dog');
INSERT PetSpecies (Species) VALUES ('Cat');

CREATE TABLE Pet (
    PetID int NOT NULL IDENTITY (1,1),
    PetSpeciesID tinyint NOT NULL,
    PetName varchar(100) NOT NULL
    OwnerID int NOT NULL,
    ...

    CONSTRAINT PK_Pet PRIMARY KEY CLUSTERED (PetID),
    CONSTRAINT FK_Pet_PetSpecies FOREIGN KEY (PetSpeciesID) 
                                 REFERENCES PetSpecies (PetSpeciesID),
    CONSTRAINT FK_Pet_Owner FOREIGN KEY (OwnerID) REFERENCE Owner (OwnerID),
    CONSTRAINT UQ_SuperKey UNIQUE (PetID, PetSpeciesID)
    );

CREATE TABLE Dog (
    PetID int NOT NULL,
    PetSpeciesID tinyint NOT NULL,
    CatsChased int NOT NULL,

    CONSTRAINT PK_Dog PRIMARY KEY CLUSTERED (PetID, PetSpeciesID),
    -- CONSTRAINT UQ_Dog UNIQUE (PetID),
    CONSTRAINT CK_Cat CHECK (PetSpeciesID = 1 /*dog*/),
    CONSTRAINT FK_Dog_Pet FOREIGN KEY (PetID, PetSpeciesID) 
                          REFERENCES Pet(PetID, PetSpeciesID)
    );

CREATE TABLE Cat (
    PetID int NOT NULL,
    PetSpeciesID tinyint NOT NULL,
    MiceEaten int NOT NULL,

    CONSTRAINT PK_Cat PRIMARY KEY CLUSTERED (PetID, PetSpeciesID),
    -- CONSTRAINT UQ_Cat UNIQUE (PetID),
    CONSTRAINT CK_Cat CHECK (PetSpeciesID = 2 /*cat*/),
    CONSTRAINT FK_Cat_Pet FOREIGN KEY (PetID, PetSpeciesID) 
                          REFERENCES Pet(PetID, PetSpeciesID)
    );

Notes:

• The PK of cat and dog can be PetID or PetID, PetSpeciesID. The latter is better to match the foreign key.

• The unique constraint on cat and dog is optional, really, as the index is contained within the PK and PetID is unique anyway if PetSpeciesID is constrained to a single value.

Answer 2

First of all Why do you want to restrict the Pets table to either cat or a dog. An owner can have both too. Besides the relationship defined by EF is absolutely correct. What you want is a data manipulation restriction which has nothing to do with the relationship. I don't think you can ever do this

The requirement you are facing should be tackled at the Model level. You can use validation for this purpose. Restrict the user from entering values for both.

Answer 3

The minimal change to your database is to add

ALTER TABLE Pets ADD 
       CONSTRAINT chkPets CHECK (
            (ID_Cats is null and ID_Dogs is not null) 
            or 
            (ID_Cats is not null and ID_Dogs is null)
       );

This ensures, that each pet is exactly one cat or one dog

Edit:

A better solution might start like this:

create table Pets (
    ID_Pets int NOT NULL IDENTITY PRIMARY KEY,
    Type varchar(10)
)

create table Cats (
    ID_Cats int NOT NULL PRIMARY KEY references Pets(ID_Pets),
    Name nvarchar(50) NOT NULL,
    miceEaten int NULL
)

create table Dogs (
    ID_Dogs int NOT NULL PRIMARY KEY references Pets(ID_Pets),
    Name nvarchar(50) NOT NULL,
    favouriteToy nvarchar(50) NULL
)

create table Owners (
    ID_Owners int NOT NULL IDENTITY PRIMARY KEY,
    Name nvarchar(50) NOT NULL,
)

create table OwnersToPets (
    ID_Owners int references Owners(ID_Owners) NOT NULL,
    ID_Pets int references Pets(ID_Pets) NOT NULL
)

but here I have difficulties to formulate the constraint that dogs and cats only reference the pets of the correct type.