Snowflake Primary Key Examples and Syntax

The world has become increasingly computerized. Now, every part of our lives is connected to the Internet, providing consumers with conveniences such as instant access to any kind of 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!

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.

Snowflake enhances efficiency by using Amazon S3 to store encrypted, compressed, and distributed data, which is only accessible through Snowflake’s SQL interface. It also supports importing semi-structured data formats like JSON, Avro, ORC, Parquet, and XML. Snowflake’s virtual warehouses, powered by Amazon EC2 Clusters, provide the compute power for queries, using MPP EC2 nodes for fast data analysis. These warehouses can scale on demand and be paused when idle. Additionally, Snowflake’s Data Marketplace offers access to shared datasets and data services, enabling connections 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 150+ pre-built Integrations that you can choose from.

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.

Explore Hevo’s 14-day free trial and experience the feature-rich Hevo suite first hand. You may also have a look at the amazing pricing, which will assist you in selecting the best plan for your requirements.

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

FAQ on Snowflake Primary Key