2

I am designing a database using Entity Framework's "model-first" approach. Based on excellent feedback I recieved here, I am adopting a super-type/sub-type pattern for part of the DB.

This pattern requires composite keys based on 2 columns in at least 2 tables (see schema below).

I've searched the forum for "entity framework" and "composite keys", but none of the questions I found were on this more basic level: Can I set up an entity model so that it will generate one table (using "Generate Database from Model...") with a composite primary key on 2 columns, such that one is a foreign key on a second table? And on another table, the same situation except the FK is based on 2 columns in the first table?

OR, is it better to just let EF generate the DB w/o the composite keys, then go into SQL Server, (re-)set the primary keys, delete the model, & create a new model based on the newly instantiated database? I know how to do that, of course; but as I'm still working out a minor detail or 2 in the DB structure, I'd prefer to put off anything that essentially results in baking in the DB structure at this point.

Here's the recommended super-type/sub-type structure, which I've mirrored in my entity model (w/o yet figuring out how to get the composite keys generated):

CREATE TABLE publications (
  pub_id INTEGER NOT NULL PRIMARY KEY,
  pub_type CHAR(1) CHECK (pub_type IN ('A', 'B', 'P', 'S')),
  pub_url VARCHAR(64) NOT NULL UNIQUE,
  CONSTRAINT publications_superkey UNIQUE (pub_id, pub_type)
);


CREATE TABLE articles (
  pub_id INTEGER NOT NULL,
  pub_type CHAR(1) DEFAULT 'A' CHECK (pub_type = 'A'),
  placeholder CHAR(1) NOT NULL, -- placeholder for other attributes of articles
  PRIMARY KEY (pub_id, pub_type),
  FOREIGN KEY (pub_id, pub_type) REFERENCES publications (pub_id, pub_type)
);

CREATE TABLE stories (
  pub_id INTEGER NOT NULL,
  pub_type CHAR(1) DEFAULT 'S' CHECK (pub_type = 'S'),
  placeholder CHAR(1) NOT NULL, -- placeholder for other attributes of stories
  PRIMARY KEY (pub_id, pub_type),
  FOREIGN KEY (pub_id, pub_type) REFERENCES publications (pub_id, pub_type)
);
Community
  • 1
  • 1
Faust
  • 15,130
  • 9
  • 54
  • 111

1 Answers1

3

I didn't try it but I think you will not be able to create this with model first. The reason is that your first table is using Unique constraint and your second and third table is building FK based on that constraint. Entity framework does not support Unique constraints so my assumption is that it will not be able to generate this DB schema. But nothing is more easy then simply try it.

Ladislav Mrnka
  • 360,892
  • 59
  • 660
  • 670