Support to Primay Keys.

Question

Hi, I would like to know that Why is there a need for Primary Keys in Azure Synapse Analytics. Does it really support? I have seen that we have options for Primary Keys but the data is being retrieved from the files, then why is there need for it? Need complete details regarding this.

Thanks.

Answer 1

Hello @Saad Khan ,

Welcome to the MS Q&A platform.

Dedicated SQL pool supports these table constraints:

PRIMARY KEY is only supported when NONCLUSTERED and NOT ENFORCED are both used. (Not enforced meaning, Pks allow to insert duplicate values)
UNIQUE constraint is only supported when NOT ENFORCED is used.

FOREIGN KEY constraint is not supported in dedicated SQL pool.

For your question, the main advantage of Primary Key in the synapse is, they allow dedicated SQL pool engine to generate an optimal execution plan for a query. All values in a primary key column or a unique constraint column should be unique.

Since there is no foreign key constraint in the dedicated SQL pool, you can't define relationships between two tables. (one to many- like the primary key from one table and foreign key from another table )

Please note: After creating a table with primary key or unique constraint in dedicated SQL pool, users need to make sure all values in those columns are unique. A violation of that may cause the query to return inaccurate result

In Azure Synapse Analytics, serverless SQL pools do not support primary keys. This means that you cannot define primary keys on tables in a serverless SQL pool, and you cannot use primary keys to enforce the uniqueness or establish relationships with other tables.

Reference documents:
https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-table-constraints
https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-overview

I hope this helps. Please let me know if you have any further questions.

---

• Please don't forget to click on and upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators