Uniqueness within a ColumnStore table
How do I maintain a unique column? I'm concerned about using an identity column as there are many ways that to accidentally circumvent this. As with any SQL table, I need the ability to ensure uniqueness and I have spent hours online trying to find a method to enforce this.
Answer Answered by David Thompson in this comment.
ColumnStore like many other analytical database engines does not support unique constraints. This helps with performance and scaling out to much larger volumes than innodb supports. It is assumed that your data preparation / ETL phase will ensure correct data being fed into columnstore.
One common pattern is to use an innodb staging table with unique constraints to allow for elimnation of duplicates there then use non transactional insert into select into your columnstore table.
