Due to some limitations in current mechanism to manage profiles inheritance, this unique index could not been added in SonarQube 4.3.
As soon as these limitations are removed, adding this unique index on active rules (rule_id, profile_id) will prevent any rules to be activated multiple times on a profile.
Technical details :
Management of profiles inheritances is done in org.sonar.server.qualityprofile.ProfilesManager. When a rule has been activated on a profile, the method activated() is called to update child profiles. To do this update, the child active rule is removed and re-create from the parent (in activateOrChange() method) in order to take into account any changes on severity and on parameters.
But by doing this, 2 active rules on same rule and profile exists in the session, so it fails when a unique index exists.