2

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.

3
  • 1
    What is the datatype for column Test_Id? Can you post your table definition? Jun 26, 2017 at 12:25
  • @SqlWorldWide it's a varchar(64)
    – Neil P
    Jun 26, 2017 at 12:57
  • Thank you. I can reproduce your error in SQL data Warehouse. Jun 26, 2017 at 15:48

3 Answers 3

7

This and constraints in general are currently unsupported in Azure SQL Data Warehouse. See https://learn.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.

2
3

The documentation of the CREATE TABLE syntax for Azure SQL Data Warehouse does not mention CHECK constraints at all, nor any other type of constraints except for NOT NULL and DEFAULT constraints.

I assume that CHECK constraints are not supported in this product.

Simple talk article: Taking Azure SQL Data Warehouse for a Test-Drive by Robert Sheldon, says it clearly:

Working with tables

When working with the SQL Data Warehouse version of a T-SQL statement, you’ll often find that the syntax is similar to how the statement is implemented in SQL Server or SQL Database. However, the SQL Data Warehouse version of a statement is usually not as extensive and sometimes supports options specific to SQL Data Warehouse, as we saw with the CREATE DATABASE statement.

The same can be said of the CREATE TABLE statement. It does not support many options available to SQL Server or SQL Database, and there are a couple options specific to SQL Data Warehouse. For example, the statement does not support primary keys, foreign keys, check constraints, unique constraints, unique indexes, computed columns, sparse columns, user-defined data types, indexed views, identities, sequences, triggers, or synonyms. The statement also does not support certain data types, such as geometry, geography, hierarchyid, and so on. At the same time, the CREATE TABLE statement provides table options that are unique to SQL Data Warehouse or handled differently from standard T-SQL.

0

I recreated this test using an empty table and did not encounter this error. For confirmation's sake, I would suggest formatting the statement as such:

ALTER TABLE [FactTbl_Test]
ADD CONSTRAINT Ck_Test_id CHECK ([Test_Id] != 'Unknown');

Let us know if you encounter the same error, and ensure only this statement is ran in your batch.

While unlikely the issue here, remember if [FactTbl_Test] already contains a [Test_Id] value of 'Unknown', the ADD CONSTRAINT will fail. You can use the option WITH NOCHECK after your table name, which will create the constraint without validating it against the existing data.

9
  • The table is empty. running as the only statement in the batch produces the same error.
    – Neil P
    Jun 26, 2017 at 12:59
  • There seems to be mixed opinions on the web about weather Azure Data Warehouse supports check constraints, but glad to hear to does work. Also confirms Microsoft's documentation - learn.microsoft.com/en-us/sql/relational-databases/tables/…
    – Neil P
    Jun 26, 2017 at 13:00
  • @NeilP At the risk of sounding like a broken record, (1) did breaking the code into two lines and adding a semi-colon make any difference? and (2) can you update the question with the statement exactly as it is being ran, or possibly provide a screenshot of exactly what you're seeing? From both my and SqlWorldWide 's tests, there does not appear to be a problem with the code you posted originally.
    – coyote
    Jun 26, 2017 at 13:35
  • new lines didn't make a difference. It looks like SqlWorldWide may have ran code against a different product.
    – Neil P
    Jun 26, 2017 at 13:45
  • 1
    "Microsoft Azure SQL Data Warehouse - 10.0.9041.10 Jun 9 2017 03:28:26 Copyright (c) Microsoft Corporation"
    – Neil P
    Jun 26, 2017 at 16:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.