Databases
StackOverflow2013
Postsdbo
POUnique ID field not included in Primary Key when a Composite key is present
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POUnique ID field not included in Primary Key when a Composite key is present
    primarykey
    data
    text
    <p>I am am trying to construct a database in SQL Server 2008 R2 that will allow users to place their own sub-types into categories. I have a parent table that holds the preset category names (defined by me). </p> <p>The question I face is what is what is the best way to deal with the <code>PRIMARY KEY</code> and <code>UNIQUE</code> constraint, and foreign key REFERENCES. Indexing is at the center of this as I anticipate that the sub table (we will call it <code>CategoryTypes</code>) will grow quite large over time and will need to be able to efficiently allow reads from the data based on the parent table (<code>Categories</code>). Is there any problem I would need to anticipate if the tables were laid out as follows? </p> <p>My concern is that the <code>IDENTITY</code> column in the <code>CategoryTypes</code> table will need to maintain a unique count. The reason I have included this field is to allow a simpler reference when passing data between tiers in the application. By passing an Integer versus an Integer / String pair. The data in these tables will persist at each layer of the database to save on bandwidth. From a database perspective, does the layout below pose any major challenges once deployed?</p> <p>To simplify, is there a problem with using a unique ID field (<code>IDENTITY</code>) that is not included in the primary key when a composite key is present? See table layout below:</p> <p>Parent Table:</p> <pre><code>CREATE TABLE schema.Categories ( Id TINYINT PRIMARY KEY NOT NULL, Name VARCHAR(100) NOT NULL, ) </code></pre> <p>Sub Table (User inserted data over time):</p> <pre><code>CREATE TABLE schema.CategoryTypes ( Id INT IDENTITY(1,1) NOT NULL, CategoryId TINYINT REFERENCES schema.Categories(Id) NOT NULL, Name VARCHAR(100) NOT NULL, CONSTRAINT PRIMARY KEY CLUSTERED(CategoryId, Name) CONSTRAINT UC_CategoryTypesId UNIQUE NONCLUSTERED(Id) ) </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USmarc_s
    1. USZack Jannsen
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COPS - I would also add that the Id in the sub-table "CategoryTypes" will be referenced by other tables as via foreign key relationships ... so it is necessary. I am open to any best practices that address such complexities in table design.
      singulars
      1. POUnique ID field not included in Primary Key when a Composite key is present
      1. USZack Jannsen
    2. COPlease beware that secondary indexes are expensive in clustered tables. If you don't really need the surrogate key `{Id}`, then don't use it at all and just use the natural key `{CategoryId, Name}`. If you want both keys, use a heap-based table (i.e. `PRIMARY KEY NONCLUSTERED`).
      singulars
      1. POUnique ID field not included in Primary Key when a Composite key is present
      1. USBranko Dimitrijevic
    3. COBranko, I am aware of the extra overhead, but thank you for reiterating this in this context. The challenge I face is I know that I will need to reference the surrogate Id (foreign key). I suppose you can do a composite foreign key ... but I am not so familiar with this. The surrogate seems to make things a lot easier. That said, I could start by eliminating "CLUSTERD" indexing in favor of "NONCLUSTERED" as both you and Justin recommend. If performance is needed later I can always address after I have some production data. In CRUD - Clustered indexes help "read", but can put overhead.
      singulars
      1. POUnique ID field not included in Primary Key when a Composite key is present
      1. USZack Jannsen
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload