Вопрос по database, sql – Каковы недостатки использования составного / составного первичного ключа?

15

Error: User Rate Limit Exceeded

Ваш Ответ

8   ответов
0

Error: User Rate Limit Exceeded

Error: User Rate Limit Exceededstackoverflow.com/questions/218100/…).
5

Error: User Rate Limit Exceeded

Error: User Rate Limit Exceeded

4

Error: User Rate Limit Exceeded

  • You have to keep dependent child tables on the end of a foriegn key up to date. If you change the the value of one of the primary key fields (which can happen - see below) you have to somehow change all of the dependent tables where their PK value includes these fields. This is a bit tricky because changing key values will invalidate FK relationships with child tables so you may (depending on the constraint validation options available on your platform) have to resort to tricks like copying the record to a new one and deleting the old records.

  • On a deep schema the keys can get quite wide - I've seen 8 columns once.

  • Changes in primary key values can be troublesome to identify in ETL processes loading off the system. The example I once had occasion to see was an MIS application extracting from an insurance underwriting system. On some occasions a policy entry would be re-used by the customer, changing the policy identifier. This was a part of the primary key of the table. When this happens the warehouse load is not aware of what the old value was so it cannot match the new data to it. The developer had to go searching through audit logs to identify the changed value.

Error: User Rate Limit Exceeded

14
  1. Could cause more problems for normalisation (2NF, "Note that when a 1NF table has no composite candidate keys (candidate keys consisting of more than one attribute), the table is automatically in 2NF")
  2. More unnecessary data duplication. If your composite key consists of 3 columns, you will need to create the same 3 columns in every table, where it is used as a foreign key.
  3. Generally avoidable with the help of surrogate keys (read about their advantages and disadvantages)
  4. I can imagine a good scenario for composite key -- in a table representing a N:N relation, like Students - Classes, and the key in the intermediate table will be (StudentID, ClassID). But if you need to store more information about each pair (like a history of all marks of a student in a class) then you'll probably introduce a surrogate key.
Error: User Rate Limit Exceeded
0

Error: User Rate Limit Exceeded

Error: User Rate Limit Exceeded

Error: User Rate Limit Exceeded

Error: User Rate Limit Exceeded

Error: User Rate Limit Exceeded
Error: User Rate Limit Exceeded
Error: User Rate Limit Exceeded
Error: User Rate Limit Exceeded
1

Error: User Rate Limit Exceeded

Error: User Rate Limit Exceeded

0
  1. when you se it on a diagram are less readable
  2. when you use it on a query join are less readable
  3. when you use it on a foregein key you have to add a check constraint about all the attribute have to be null or not null (if only one is null the key is not checked)
  4. usualy need more storage when use it as foreign key
  5. some tool doesn't manage composite key
1

Error: User Rate Limit Exceeded

Error: User Rate Limit ExceededPRIMARY KEYError: User Rate Limit ExceededPRIMARY KEYError: User Rate Limit ExceededError: User Rate Limit Exceeded

Error: User Rate Limit ExceededPRIMARY KEYError: User Rate Limit ExceededPRIMARY KEYError: User Rate Limit ExceededPRIMARY KEYError: User Rate Limit ExceededPRIMARY KEYError: User Rate Limit ExceededadvantageError: User Rate Limit ExceededPRIMARY KEYError: User Rate Limit Exceeded

Error: User Rate Limit Exceeded

Похожие вопросы