question

PranviKanwar-2531 avatar image
0 Votes"
PranviKanwar-2531 asked PRADEEPCHEEKATLA-MSFT commented

Data Insert Synapse

I want to insert data into table in Azure Synapse

Input

Table A
ID
1
1
1
2
3
Only ID one record should be stored in destination.How to have constraint defined in destination table.

Output

Table B
ID
1
2
3

This does not work Msg 104467, Level 16, State 1, Line 1
Enforced unique constraints are not supported. To create an unenforced unique constraint you must include the NOT ENFORCED syntax as part of your statement

azure-synapse-analytics
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

PRADEEPCHEEKATLA-MSFT avatar image
0 Votes"
PRADEEPCHEEKATLA-MSFT answered PRADEEPCHEEKATLA-MSFT commented

Hello @PranviKanwar-2531,

Thanks for the question and using MS Q&A platform.

This is by design and Primary Key enforcement is currently unavailable in Azure Synapse Analytics.

Note: Dedicated SQL pool supports these table constraints:

  • PRIMARY KEY is only supported when NONCLUSTERED and NOT ENFORCED are both used.

  • UNIQUE constraint is only supported with NOT ENFORCED is used.

For more details, refer Primary key, foreign key, and unique key using dedicated SQL pool in Azure Synapse Analytics.

Hope this helps. Do let us know if you any further queries.


Please "Accept the answer" if the information helped you. This will help us and others in the community as well.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @PranviKanwar-2531,

Just checking in to see if the above answer helped. If this answers your query, do click Accept Answer and Up-Vote for the same. And, if you have any further query do let us know.

1 Vote 1 ·