azure-sql-data-warehouse
I'd like to add a check constraint to a table in azure data warehouse.
I have the below script:
alter table [FactTbl_Test] add constraint Ck_Test_id check ([Test_Id] != 'Unknown')
This causes the below error:
Parse error at line: 2, column: 74: Incorrect syntax near 'check'.
Edit:
Also appears to fail with the same error if the constraint is created inside the table declaration.
Functions in Azure DW don't support select statements that access tables like in your use case, see CREATE FUNCTION (SQL Data Warehouse):
function_body
Specifies that a series of Transact-SQL statements, which do not reference database data (tables or views), define the value of the function.
Could you double check that function is created in DW?
Add a label to your query using the OPTION ( LABEL ... ) syntax which is supported in Azure SQL Data Warehouse. You can then monitor it either via the portal or using the DMV sys.dm_pdw_exec_requests, for example
SELECT *
FROM dbo.yourBillionRowTable
OPTION ( LABEL = 'Your Unique Query Label 042' )
-- In a separate window...
SELECT *
FROM sys.dm_pdw_exec_requests
WHERE [label] = 'Your Unique Query Label 042'
Results:
Or monitor it in the portal:
Best Answer
This and constraints in general are currently unsupported in Azure SQL Data Warehouse. See https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-tables-overview#unsupported-table-features for full list.
If the constraint is not enforced at the source or if you have multiple sources, you can implement during the ETL or ELT process. For small datasets, ETL would probably work better. With large datasets, you're likely to get better performance via ELT-L. As in extract then load to a temp table (heap is fast), perform the check/cleansing then load to the final table.