Issue Summary

Currently, it is possible to create duplicates in regard to card colors per rapid board, since there is no unique constraint on AO_60DB71_CARDCOLOR, I understand possibly allowing some JQL and colors to be duplicated, but priority and assignee with the same color, makes little sense. 

                                   Table "public.AO_60DB71_CARDCOLOR"
    Column     |          Type          |                             Modifiers                              
---------------+------------------------+--------------------------------------------------------------------
 COLOR         | character varying(255) | 
 ID            | bigint                 | not null default nextval('"AO_60DB71_CARDCOLOR_ID_seq"'::regclass)
 POS           | integer                | not null default 0
 RAPID_VIEW_ID | bigint                 | not null
 STRATEGY      | character varying(255) | 
 VAL           | character varying(255) | 
Indexes:
    "AO_60DB71_CARDCOLOR_pkey" PRIMARY KEY, btree ("ID")
    "index_ao_60db71_car2031978979" btree ("RAPID_VIEW_ID")
Foreign-key constraints:
    "fk_ao_60db71_cardcolor_rapid_view_id" FOREIGN KEY ("RAPID_VIEW_ID") REFERENCES "AO_60DB71_RAPIDVIEW"("ID")

Steps to Reproduce

Can reproduce in Data Center: Yes

1. Create a new board 
2. Go to Board > Configure > Card colors and select Colors based on to Assignees;.
3. Run the following REST API to create duplicates: 

   curl -X POST \
     BASEURL/rest/greenhopper/1.0/cardcolors/1/color \
     -H 'authorization: Basic encoded-base64-password' \
     -H 'cache-control: no-cache' \
     -H 'content-type: application/json' \
     -d '{	"color": "#cc0000", 
   	"value": "admin",
   	"strategy": "assignee"}'
   

4. Refresh the board card colors config and we will see the duplicate assignees values.

I'm expecting this could be scripted easily in something like script runner also. 

Expected Results

Jira would not allow duplicated card colors for the same type and value

Actual Results

Jira allows duplicates since there is no unique constraint on AO_60DB71_CARDCOLOR

Workaround

You can either use the GUI to delete these manually or backup the table and delete the entries manually, example query:

 delete from "AO_60DB71_CARDCOLOR" where "ID" in (<IDS of duplicates>);

To delete a large number of duplicate card colors entries:

1. Find the affected BOARD ID from the URL -> BASEURL/secure/RapidView.jspa?rapidView=1
   • Board id = 1 (e.g rapidView=1)
2. Put the 1 to the a."RAPID_VIEW_ID" column value like below.
3. Find all the duplicate entries:

   SELECT a."VAL", a."ID"
   FROM "AO_60DB71_CARDCOLOR" a
   INNER JOIN "AO_60DB71_CARDCOLOR" b ON a."VAL" = b."VAL"
   WHERE a."RAPID_VIEW_ID" = '1'
   AND a."ID" > b."ID";
   

4. Backup Jira database and run the next SQL query to remove all the duplicate entries:

   delete from "AO_60DB71_CARDCOLOR" where "ID" in (SELECT a."ID"
   FROM "AO_60DB71_CARDCOLOR" a
   INNER JOIN "AO_60DB71_CARDCOLOR" b ON a."VAL" = b."VAL"
   WHERE a."RAPID_VIEW_ID" = '1'
   AND a."ID" > b."ID");
   

5. Restart all the nodes.