Snowflake Primary Key Examples and Syntax

The world is becoming increasingly computerized. Soon, every part of our lives will be connected to the Internet, providing consumers with more conveniences such as instant access to information. While this is a positive development, the sheer volume of data generated as a result of digitalization is staggering. Snowflake is one such Cloud Data Warehouse that is helping out users manage this colossal volume of data. Snowflake Primary Key constraint, on the other hand, ensures interoperability with other databases. Furthermore, all Referential integrity restrictions like Primary Key, Foreign Key, Unique Key, and NOT NULL are supported by Snowflake.

In this article, you will learn about Snowflake Primary Keys. More importantly, you will gain a practical understanding of what Primary Keys are and how you can use Snowflake Primary Keys at the conclusion. Read along to find out more!

Table of Contents

• What is Snowflake?
  • Key Features of Snowflake
• Overview of Constraints
  • Supported Constraint Types
  • Table Constraints
  • Single-Column and Multi-Column Constraints
  • Inline and Out-of-Line Constraints
• How to use the Snowflake Primary Key Constraint?
  • What is the Syntax for Snowflake Primary Key?
    • Snowflake Primary Key at the Column Level
    • Snowflake Primary Key at Table Level
    • Add a Primary Key to an Existing Table using ALTER Clause
    • Testing Key Enforcement by Inserting Duplicate Records
    • Snowflake Primary Key Example
• Conclusion

What is Snowflake?

Snowflake is a fully managed service that allows clients to connect, load, analyze, and securely share their data with near-infinite scalability of concurrent processes. Data Lakes, Data Engineering, Data Application Development, Data Science, and secure Data Consumption are some of its most prevalent use cases.

Snowflake’s Architecture is unique as it naturally separates Computation and Storage. This design allows you to give your Users and Data Workloads virtual access to a single copy of your data without sacrificing speed. For a consistent experience, Snowflake allows you to run your data solution across Multiple Locations and Clouds. Snowflake makes this feasible by abstracting the underlying Cloud infrastructure’s complexity.

To enhance efficiency, Snowflake employs Amazon S3 as the file system to store encrypted, compressed, and distributed data. The data on these file systems is not accessible to the user and can only be accessed through Snowflake’s SQL interface. Semi-structured data, such as JSON, Avro, ORC, Parquet, and XML, can also be imported into Snowflake.

The virtual warehouse notion in Snowflake refers to Amazon EC2 Clusters that provide compute power for queries. Each virtual warehouse has an MPP EC2 Compute Cluster with several nodes for rapid data analysis. Snowflake manages the scaling up and down of these virtual warehouses on-demand, as well as allowing you to pause them while not in use.

Snowflake also gives access to shared datasets and data services through the Snowflake Data Marketplace, which allows you to connect with thousands of other Snowflake users.

Key Features of Snowflake

Here are some of the benefits of using Snowflake as a Software as a Service (SaaS) solution:

• Snowflake enables you to enhance your Analytics Pipeline by transitioning from nightly Batch Loads to Real-time Data Streams, allowing you to improve the quality and speed of your analytics. By enabling secure, concurrent, and monitoring access to your Data Warehouse across your organization, you can improve the quality of analytics at your company.
• Snowflake uses the Caching Paradigm to swiftly deliver the results from the cache. To avoid re-generation of the report when nothing has changed, Snowflake employs Persistent (within the session) Query results.
• Snowflake allows you to get rid of silos and ensure access to meaningful insights across the enterprise, resulting in better Data-driven Decision-Making. This is a crucial first step toward bettering partner relationships, optimizing pricing, lowering operational expenses, increasing sales effectiveness, and more.
• Snowflake allows you to better analyze Customer Behaviour and Product Usage. You can also use the whole scope of data to ensure customer satisfaction, drastically improve product offers, and foster Data Science innovation.
• Snowflake allows you to create your own Data Exchange, which allows you to securely communicate live, controlled data. It also encourages you to improve data relationships throughout your business units, as well as with your partners and customers.

Overview of Constraints

Supported Constraint Types

Snowflake offers support for the following constraint functionalities from the ANSI SQL standard:

• PRIMARY KEY
• UNIQUE
• NOT NULL
• FOREIGN KEY

A table can possess multiple foreign and unique keys, but only one primary key. All foreign keys need to reference a corresponding unique or primary key that matches the column types of the columns present in the foreign key. The primary key for a foreign key can be on a different table or the same table as the foreign key.

Table Constraints

Snowflake also provides support for defining constraints on transient, permanent, and temporary tables. Constraints can be defined on columns of all data types and there are no limits on the number of columns that can be included in a constraint:

• When a table gets copied using CREATE TABLE … LIKE or CREATE TABLE … CLONE, all the current constraints of the table, including foreign keys, are copied to the new table.
• Supplementary functions and commands, such as GET_DDL and DROP/UNDROP are supported for tables with constraints. They can also be supported for databases and schemas.

Single-Column and Multi-Column Constraints

Constraints can be described on a single column or on multiple columns within the same table. For multi-column constraints (i.e. compound unique or primary keys), the columns are ordered, and every column has a corresponding key sequence.

Inline and Out-of-Line Constraints

Constraints are either defined out-of-line or inline during table generation or alteration:

• Out-of-line constraints are defined using a separate clause that mentions the column(s) on which the constraint is generated. They can be used for generating either multi-column or single-column constraints, as well as creating constraints for existing columns.
• Inline constraints are generated as part of the column definition and can only be leveraged for single-column constraints.

How to use the Snowflake Primary Key Constraint?

On the Snowflake Cloud Data Warehouse, you can create a Snowflake Primary Key while creating tables. When you load the tables, the Snowflake Primary Key, however, will not be enforced. Constraints, on the other hand, provide useful metadata. The optimizer creates an optimized execution plan using the primary key. There can be several unique keys and foreign keys in a table, but there can only be One Primary Key.

Similarly, On the Snowflake Cloud Data Warehouse, you can create the Foreign Key while creating tables. When you load the tables, however, Foreign Keys again will get enforced. There can be multiple unique keys and foreign keys in a table. Multiple tables can be referred to by a single table.

What is the Syntax for Snowflake Primary Keys?

You can use a variety of approaches to add Primary Keys to a Snowflake Table:

• Snowflake Primary Key at the Column Level
• Snowflake Primary Key at Table Level
• Add a Primary Key to an Existing Table with ALTER Clause
• Testing Key Enforcement by Inserting Duplicate Records
• Snowflake Primary Key Example

A) Snowflake Primary Key at the Column Level

You can use the following DDL Query to add a Column-level Primary Key:

CREATE TABLE pk_tutorial_tab 
  ( 
     id      INT PRIMARY KEY, 
     FULL_NAME    VARCHAR(10), 
     Location VARCHAR(100) 
  ); 

B) Snowflake Primary Key at Table Level

You can also add the Primary Key at a Table Level. You can use the following command to do the same:

CREATE TABLE pk_tutorial_tab 
  ( 
     id      INT PRIMARY KEY, 
     FULL_NAME    VARCHAR(10), 
     Location VARCHAR(100) 
  ); 

C) Add a Primary Key to an Existing Table with ALTER Clause

You can use the ALTER Table Command in the Snowflake to modify and add a Primary Key to an existing table:

ALTER TABLE pk_tutorial_tab1 ADD PRIMARY KEY (id);

D) Testing Key Enforcement by Inserting Duplicate Records

As mentioned earlier, the Primary Key is not enforced. Duplicate values in a table can be used to test the same. The examples below insert duplicate records into a table that has a Primary Key defined on one of its columns.

INSERT INTO pk_tutorial_tab1 values (1,'x','xyz'), (2,'y','yza'), (1,'x','xyz');
+-------------------------+
| number of rows inserted |
|-------------------------|
|                       3 |
+-------------------------+

Rows are added without a hitch, as you can see. As a result, the main key restriction is ignored.

E) Snowflake Primary Key Example

This example depicts how you can generate a simple NOT NULL constraint while creating a table, and another NOT NULL constraint while modifying the table:

• Make a table and generate a constraint at the same time with the following code snippet:

create table table1 (
    col1 integer not null
    );

• Modify the table to add a column with a constraint as follows:

alter table table1 
    add column col2 varchar not null;

The following example mentions that the intent of the column is to hold unique values, but clarifies that the constraint hasn’t actually been enforced. This example also shows how you can mention a name for the constraint:

alter table table1 
    add column col3 varchar not null constraint uniq_col3 unique not enforced;

The following example will create a parent table with a Snowflake Primary Key constraint and another table with a foreign key constraint that points to the same columns as the first table’s primary key constraint:

create table table2 (
    col1 integer not null,
    col2 integer not null,
    constraint pkey_1 primary key (col1, col2) not enforced
    );
create table table3 (
    col_a integer not null,
    col_b integer not null,
    constraint fkey_1 foreign key (col_a, col_b) references table2 (col1, col2) not enforced
    );

Conclusion

You should now know what Snowflake Primary Keys are and what they’re used for. These resources are intended to help you learn more about Snowflake. You’ll have a greater chance of getting the most out of Snowflake Data if you keep this information in mind!

While Snowflake Services are useful, maintaining the correct environment on a regular basis is a difficult undertaking. Further, extracting data from a variety of sources and integrating it into your Data Warehouse can be a daunting task. This is where Hevo comes to your aid to make things easier! Hevo Data is a No-code Data Pipeline and has awesome 100+ pre-built Integrations that you can choose from.

Visit our Website to Explore Hevo

Hevo can help you integrate your data from numerous sources and load them into destinations like Snowflake to analyze real-time data with BI tools of your choice. It will make your life easier and data migration hassle-free.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and see the difference!

Share your experience of learning about the Snowflake Primary Keys in the comments section below. We would love to hear from you!