none
Can't we create Managed Table with Foreign key in Azure data lake?

    Question

  • I was looking into documentation while creating tables but so far example didn't mentioned ,how to add foreign key while creating table. Also I check documentation for Alter statement too, but same goes with this too

      CREATE TABLE Brand (
                BrandId          int,            
                Name         string,
                AddressId           int,
                SuperAdminDetails string,
                CONSTRAINT FK_Address FOREIGN KEY (AddressId) REFERENCES Address(AddressId) ,
                
                INDEX Brand_idx CLUSTERED (BrandId ASC) 
                      PARTITIONED BY HASH (BrandId)
      );
    //
    //
    //  ALTER TABLE Brand ADD CONSTRAINT FK_Address FOREIGN KEY (AddressId) REFERENCES Address(AddressId) ;

    Error While Executing Above USQL

    Error While Executing Above USQL


    HS

    Wednesday, April 26, 2017 12:01 PM

All replies

  • Hi,

    U-SQL tables don't provide full feature parity with SQL Server tables, and don't currently support Foreign Key constraints as we do in SQL Server.

    Instead, you should join you data against the reference table when loading to ensure data integrity, and potentially flag records where a match does not exist. This is a relatively common pattern for loading Fact-like with reference to other tables in distributed environments such as Azure Data Lake.

    • Proposed as answer by Jorg KleinMVP Wednesday, June 28, 2017 10:50 AM
    Friday, May 19, 2017 3:11 PM