ThingXTagID INT FK -> ThingXTagID INT AutoMagic PKĪttributeX VARCHAR ThingID INT FK (to Thing.ThingID) I have worked on a system where these bridge tables had their own auto-incrementing, surrogate key PKs, and the single column surrogate key of the bridge table was referenced in other tables via FK: WackyTable ThingXTag TagID INT FK -> TagID INT PK, FK (to Tag.TagID) Stuff SomeType TagID INT PK, FK ThingID INT PK, FK (to Thing.ThingID) ThingID INT AutoMagic PK -> ThingID INT PK, FK (or whatever you like to call tables used only, or mainly, to represent many-to-many relationships) Thing ThingXTag Tag I emphasize "truly" because there are two situations where I prefer to not have a new surrogate key: There are also bugs in other systems that feed data into yours that can allow for the data itself to not be entirely correct, even if their export process worked correctly. There are also bugs in export processes that might cause values in a file imported by your system to be incorrect. SKU, perhaps), can the incoming value be guaranteed to be correct? Humans often mistype stuff doing data entry. Even when something should be "stable" in value and uniqueness (e.g.Sometimes things change, either in value or uniqueness (we don't control the external world).Social Security Numbers / SSNs in the U.S.) What is thought to be unique is not always unique (e.g.Given that the focus of this question is "preferable" and not "acceptable", and accepting that this is still a highly subjective topic, I will say that I cannot think of a situation where it is best for the system to have a truly natural key for a variety of reasons (most of which has been said before in other answers that Paul linked to in a comment on the question): Under what circumstances, if ever, is preferable to use a primary key with some other real meaning? (emphasis added) The question is, under what circumstances, if ever, is preferable to use a primary key with some other real meaning? That is, is there anything wrong with the advice that the PK should be arbitrary, and you can generally get by with a sequence number? The obvious problems are (a) if the client name changes then the PK should change too, and (b) there is too much of a risk of conflict with clients with similar names.Ī half exception is the use of an auto-incremented number, which has a minor meaning of a sequence number. I have seen Primary Keys which include some sort of code, such as a client code based on the name. That is, it should be otherwise meaningless. will not be recycled a row which is deleted for some reason should not free the old PK value to be reusedįor these reasons, I generally recommend that the Primary Key have no intrinsic value, so there would never be a reason to change or recycle a value.unlikely to change although Foreign Key constraints can include cascading, it is better not to require this.guaranteed to be unique some other unique column values may be duplicated as the table grows.Properties which make a Primary Key more useful include: There are possibly other columns in a table with unique values, so they can also uniquely identify a row (Candidate Keys), but the Primary Key is the one designated for the task. Excuse the looseness of any definitions here, but I am trying to explore a simple concept.Ī Primary Key uniquely identifies a row.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |