In this blog post, let’s learn about the error message “1160 A mismatch between the NOT DROPPABLE PRIMARY KEY constraint and the STORE BY clause was detected. When both clauses are specified, the STORE BY key column list must be the same as, or a prefix of, the PRIMARY KEY column list. This mismatch is caused by differences between the columns themselves, the order of columns, or the ASC/DESC attribute.” when working with HPE NonStop SQL/MX, the reason for the error and the solution to fix this error message.
Error Message
1160 A mismatch between the NOT DROPPABLE PRIMARY KEY constraint and the STORE BY clause was detected. When both clauses are specified, the STORE BY key column list must be the same as, or a prefix of, the PRIMARY KEY column list. This mismatch is caused by differences between the columns themselves, the order of columns, or the ASC/DESC attribute.
Reason for the Error
You attempted to create a table that contains both a NOT DROPPABLE PRIMARY KEY constraint and a STORE BY clause. The syntax specified is not correct. The STORE BY column list must be the same as, or a prefix of, the NOT DROPPABLE PRIMARY KEY column list.
Solution
If you want the STORE BY column list that specifies the clustering key to be different than the PRIMARY KEY, specify a DROPPABLE PRIMARY KEY. If you want the PRIMARY KEY to be the same as the STORE BY key, do not specify a STORE BY clause. Correct the syntax and resubmit
