Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

entity relationship diagram

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.

share|improve this question
    
There is a lot of info missing here. Can a pet have multiple owners (eg wife+husband) or is one owner logged only? Do you expect more pet types (eg Rabbit, Bird, Fish)? –  gbn Dec 27 '11 at 7:40
    
Technically there are no composed primary keys. All Tables have surrogate primary keys -- NOT NULL IDENTITY PRIMARY KEY –  bernd_k Dec 27 '11 at 8:08
add comment

migrated from stackoverflow.com Dec 27 '11 at 6:14

This question came from our site for professional and enthusiast programmers.

3 Answers

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.

share|improve this answer
    
Please explain UQ1 –  bernd_k Dec 27 '11 at 8:52
    
@bernd_k: it is a unique constraint that the FKs in Dog, Cat bind to. It is a "super key" –  gbn Dec 27 '11 at 8:53
    
can you please provide full executable code for option 3. –  bernd_k Dec 27 '11 at 10:46
    
I wish I could vote +3 for Option 3. –  ypercube Dec 28 '11 at 0:45
    
Option3: a primary key PetID, PetSpeciesID conflicts to standard database theory because this superkey contains the smaller key PetID. How do you handle joins beween the Pet and the Dog table: do you use the PetID and the PetSpeciesId field in the join or do you ommit the redundant PetSpeciesId or schould each developer handle this in his own way? –  miracle173 Aug 6 '13 at 7:12
show 1 more comment

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.

share|improve this answer
1  
This works, but it patches a bad design. As more pet types are added, then your check constraint quickly becomes complex –  gbn Dec 27 '11 at 8:35
    
@gbn I edited my answer. Can you help? –  bernd_k Dec 27 '11 at 10:15
    
Your OwnersToPets would require multiple FK parents (eg ID_Pets to both Dogs and Cats) which can't be done. Either you need my option 1 or my option 3 to solve this if separate Dog and Cat tables (different attributes) are used –  gbn Dec 27 '11 at 10:18
    
I do not understand. I want to keep ID_Cats disjunct from ID_Dogs, so that I can derive the type from the ID alone –  bernd_k Dec 27 '11 at 10:22
    
of course, but you added ID_Pets to OwnersToPets. What do you expect to do with this, given you have no link from this or Pets to Dogs and Cats –  gbn Dec 27 '11 at 10:27
show 2 more comments

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.

share|improve this answer
    
Of course an owner can have both. But an owner can also have multiple cats and dogs. And a pet can also have multiple owners. What I was looking for was either a) changing the EF model to achieve what I wanted, or b) a different DB design to get the relationship I needed. –  Dan Dec 27 '11 at 7:03
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.