Skip to main content
Database Administrators

You are not logged in. Your edit will be placed in a queue until it is peer reviewed.

We welcome edits that make the post easier to understand and more valuable for readers. Because community members review edits, please try to make the post substantially better than how you found it, for example, by fixing grammar or adding additional resources and hyperlinks.

Required fields*

9
  • 2
    It might help if you don't mix up logical and physical concepts. Constraints, including primary key and unique constraint, are logical concepts that should be defined by your data. itemId clearly is the identifying attribute, so it is the primary key, whether you want it or not. Now, whether you want data to be clustered along that attribute (physical concept) is a different question -- looks like clustering along userId, as you say, might be a better approach. Commented Sep 12, 2016 at 20:20
  • @mustaccio Isn't it equally true to say that (userId,itemId) is 'the' identifying attribute and so the primary key? Commented Sep 12, 2016 at 20:23
  • Don't think of a primary key as always being clustered. This is coincidental due to the way the table designer works. A PK can be clustered. A clustered index can be on a PK. They are, however, not dependent on each other. Commented Sep 12, 2016 at 22:30
  • @ChrisW (userId,itemId) are two separate attributes, obviously, and only one of them is identifying -- the item ID, by your own definition. The other one identifies a user. Commented Sep 12, 2016 at 22:54
  • @mustaccio Apparently it's fine and normal to use "two separate attributes" as the primary key of an associative table when it's an N-to-N relationship. Is it wrong (e.g. harmful in some way, for some reason) to use two attributes as the primary key of an object in a 1-to-N relationship? Is it wrong to say that the owner ID is part of the object's identity? Commented Sep 12, 2016 at 23:44