[jira] [Created] (AMBARI-14972) Add PK to servicecomponentdesiredstate Table To Support FK Relationships

["Jonathan Hurley (JIRA)" <ji...@apache.org>]

Jonathan Hurley created AMBARI-14972:
----------------------------------------

             Summary: Add PK to servicecomponentdesiredstate Table To Support FK Relationships
                 Key: AMBARI-14972
                 URL: https://issues.apache.org/jira/browse/AMBARI-14972
             Project: Ambari
          Issue Type: Task
          Components: ambari-server
    Affects Versions: 2.4.0
            Reporter: Jonathan Hurley
            Assignee: Jonathan Hurley
            Priority: Critical
             Fix For: 2.4.0


The {{servicecomponentdesiredstate}} table currently uses a compound PK based off of the cluster ID, service name, and component name. There are several problems with this approach:

- Primary Keys should be data that's not part of the business logic of the system and not subject to be changed potentially (as strings are).
- Other tables referencing the {{servicecomponentdesiredstate}} table would now need knowledge of cluster/service/component in order to make the correct FK association. This leads to extra data being tracked as well as data duplication.
- Some databases, such as SQL Server, have problems with the indexing of compound PKs and may lead to deadlocks when querying and updating concurrently.

We should change this table so that it uses a simple PK for referencing. FK relationships as they exist today can still be maintained as long as a {{UNIQUE}} constraint is placed on the table. We should:

- Add a {{UNIQUE}} constraint to the former PK columns
- Add an {{INDEX}} to the former PK columns



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)