Constraints:
Snowflake NOT NULL Constraint:
Constraints other than NOT NULL are created as disabled. Snowflake enforces only NOT NULL. You can create NOT NULL constraint while creating tables in the cloud database.
A Snowflake table can have multiple NOT NULL columns.
Snowflake NOT NULL Constraint Syntax
There are many methods that you can use to add NOT NULL on Snowflake table.
· Column level NOT NULL – Add NOT NULL constraint during table creation.
· Alter Table to Add NOT NULL – Use Alter table command to add NOT NULL constraint.
Column level NOT NULL
You can add the NOT NULL to the Snowflake table DDL along with column the data type.
For example, consider below table DDL with a column ID defined as NOT NULL.
CREATE TABLE nn_demo_table ( id INT NOT NULL, NAME VARCHAR(10), address VARCHAR(100) );
Now, the Snowflake database will allow only non-null values in the ID column. You will end up getting an error if the value is NULL.
Alter Table to Add NOT NULL Constraint
You can also add the NOT NULL constraint to the existing table. The table must be empty in order to SET NOT NULL constraint.
For example, consider following ALTER statement to add NOT NULL with default value.
ALTER TABLE nn_demo_table MODIFY COLUMN ID SET NOT NULL; +----------------------------------+| status ||----------------------------------|| Statement executed successfully. |+----------------------------------+
Test Add NOT NULL Constraint
Now, let us try to insert the NULL values into the not null column and check the error.
>INSERT INTO nn_demo_table values (1,'a','abc');+-------------------------+| number of rows inserted ||-------------------------|| 1 |+-------------------------+ >INSERT INTO nn_demo_table values (null,'a','abc');100072 (22000): NULL result in a non-nullable column
As you can see in the above example, you can only insert non-null values. Therefore, Snowflake cloud data warehouse enforces the NOT-NULL constraint.
Conclusion :In both oracle and snowflake we can add NOT NULL constraint at Column level only not at table level..
Snowflake Unique Key Constraint
You can create Unique key constraint while creating tables in the Snowflake cloud database but it will not be enforced while loading tables. The Snowflake query planner uses these constraints to create a better query execution plan.
Similar to the foreign key, a table can have multiple unique key defined on it.
Snowflake Unique Key Constraint Syntax
There are three methods that you can use to add unique keys on Snowflake table.
· Column level Unique Key – Add unique key constraint during table creation.
· Table Level Unique Key – You can define unique key at the end of column definition after all columns are defined.
· Alter Table to Add Unique Key – User Alter table command to add unique key constraint.
Column Level Unique Key
You can mention if the particular column is unique on column level when you create tables.
For example, consider below DDL for column level unique key.
CREATE TABLE uk_demo_table
(
id INT UNIQUE,
NAME VARCHAR(10),
address VARCHAR(100)
);
Table Level Primary Key
You can also add the unique key on table level.
For example,
CREATE TABLE uk_demo_table
(
id INT,
NAME VARCHAR(10),
address VARCHAR(100),
UNIQUE(id)
);
Alter Table to Add Snowflake Unique Key
You can use ALTER TABLE command to add unique key.
For example,
ALTER TABLE uk_demo_table ADD UNIQUE (id);
Test Unique Constraint on Snowflake
As mentioned earlier, Unique key will not be enforced when you insert records. You can insert duplicate records.
For example,
INSERT INTO uk_demo_table values (1,'a','abc'), (1,'a','abc');
+-------------------------+
| number of rows inserted |
|-------------------------|
| 2 |
+-------------------------+
From the above example, you can see duplicated records are inserted. In
conclusion, a unique key is not enforced in snowflake but enforced in oracle..
Snowflake Primary Key Constraint
You can create the primary key while creating tables on the Snowflake cloud data warehouse. But, primary key will not be enforced when you load the tables. However, constraints provide valuable metadata. The optimizer uses the primary key to create an optimal execution plan.
A table can have multiple unique keys and foreign keys, but only one primary key.
Snowflake Primary Key Constraint Syntax
There are many methods that you can use to add foreign keys on Snowflake table.
· Column level Primary Key – Add primary key constraint during table creation.
· Table Level Primary Key – You can define primary key at the end of column definition after all columns are defined.
· Alter Table to Add Primary Key – User Alter table command to add primary key constraint.
Column Level Primary Key
For example, consider below DDL for column level primary key
CREATE TABLE pk_demo_table ( id INT PRIMARY KEY, NAME VARCHAR(10), address VARCHAR(100) );
Table Level Primary Key
You can also add the foreign key on table level.
For example,
CREATE TABLE pk_demo_table
(
id INT,
NAME VARCHAR(10),
address VARCHAR(100),
PRIMARY KEY (id)
);
Alter Table to Add Snowflake Primary Key
You can use ALTER TABLE command to add primary key.
For example,
ALTER TABLE pk_demo_table1 ADD PRIMARY KEY (id);
In the previous sections, we have mentioned that Primary key is not enforced. We can test the same by inserting duplicate values into a table.
INSERT Duplicate Records
Following INSERT statement examples adds the duplicate records to the table which has a primary key defined on one of its column.
INSERT INTO pk_demo_table1 values (1,'a','abc'), (2,'b','bcd'), (1,'a','abc');+-------------------------+| number of rows inserted ||-------------------------|| 3 |+-------------------------+As you can see, rows are inserted without any issues. Hence, the primary key constraint is not enforced in snowflake but enforced in oracle
Snowflake Foreign Key Constraint
You can create the foreign key while creating tables on the Snowflake cloud data warehouse. But, foreign key will not be enforced when you load the tables. However, constraints provide valuable metadata. The optimizer uses the foreign keys to create an optimal execution plan.
A table can have multiple unique keys and foreign keys. A table can refer multiple tables. But, a column can refer single table.
Snowflake Primary Key Constraint Syntax
There are many methods that you can use to add foreign keys on Snowflake table.
· Column level Foreign Key – Add foreign key constraint during table creation.
· Table Level Foreign Key – You can specify the foreign key after all columns are defined.
· Alter Table to Foreign Key – User Alter table command to add foreign key constraint.
Column Level Foreign Key
You can add the foreign on the column level.
For example, consider below DDL for column level foreign key.
CREATE TABLE fk_demo_table ( id INT references pk_demo_table1(id), NAME VARCHAR(10), address VARCHAR(100) );
Table Level Foreign Key
You can also add the foreign key on table level.
For example,
CREATE TABLE fk_demo_table ( id INT , NAME VARCHAR(10), address VARCHAR(100), foreign key (id) references pk_demo_table1(id) );
Alter Table to Add Snowflake Foreign Key
You can use ALTER TABLE command to add foreign key.
For example, consider below alter statement to add foreign key to existing Snowflake DDL.
ALTER TABLE fk_demo_table ADD FOREIGN KEY (id) REFERENCES pk_demo_table1(id);
Check Foreign Key
In Snowflake, foreign key constraint is not enforced. You can insert values in tables that are not present in the parent table or reference table.
The parent table contains:
+----+------+---------+
| ID | NAME | ADDRESS |
|----+------+---------|
| 1 | a | abc |
| 2 | b | bcd |
| 1 | a | abc |
+----+------+---------+
Below statement will insert record to the table;
insert into fk_demo_table values (100,'A2A','ZZZ');
Hence, the foreign key constraint is not enforced in snowflake but enforced in oracle..
Snowflake supports primary, foreign key(s) and unique key(s), but, does not enforce them. Snowflake table allows you to insert duplicate rows. There are chances that some application may insert the records multiple times. There are sever methods you can use to de-duplicate the snowflake tables.
Remove Duplicate Records from Snowflake Table
There are many methods that you can use to remove the duplicate records from the Snowflake table. For example, use the DISTINCT keyword to remove duplicate while retrieving rows.
The following methods can be used to remove duplicate records Snowflake table.
· Use DISTINCT Keyword
· ALTER TABLE using SWAP WITH method
· Use ROW_NUMBER Analytic function
· Use GROUP BY Clause to Remove Duplicate Records
· Now, let us check these methods in brief.
· Use Intermediate and Snowflake DISTINCT Keyword
· This is one of the easiest methods to remove the duplicate records from the table. This method works perfectly when your table is populated twice.
· The idea here is to create another intermediate table using the DISTINCT keyword while selecting from the original table.
For example, below query creates an intermediate table using DISTINCT keyword.
CREATE TABLE int_sample_table3 AS SELECT DISTINCT * FROM sample_table3;
ALTER TABLE sample_table3 RENAME TO sample_table3_old;
ALTER TABLE int_sample_table3 RENAME TO sample_table3;
DROP TABLE sample_table3_old;
As you can see, this method is pretty simple and worked perfectly fine if table size is small.
ALTER TABLE using SWAP – Used Along with DISTINCT
The SWAP WITH method swaps all content and metadata between two specified tables. The SWAP will rename two tables in a single transaction.
For example, Following example demonstrates usage of SWAP in alter table.
CREATE OR REPLACE TABLE sample_table3_dedup LIKE sample_table3;
INSERT INTO sample_table3_dedup SELECT DISTINCT * FROM sample_table3;
ALTER TABLE sample_table3_dedup SWAP WITH sample_table3;
DROP TABLE sample_table3_dedup;
Use ROW_NUMBER Analytic Function To Remove Dulicate
The row_number Snowflake analytic function is used to rank or number the rows. Here, we use the row_number function to rank the rows for each group of records and then select only one record from that group.
For example, consider below example to create intermediate table using analytical functions to remove duplicate rows.
SELECT col1, col2
FROM
(
SELECT col1, col2
ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY col1, col2) RNO
FROM sample_table3
)
WHERE RNO = 1;
Eg:
(
SELECT *
FROM
(
SELECT school.*,
ROW_NUMBER() OVER (PARTITION BY 1, 2 ORDER BY 1, 2 ) RNO FROM school
)
WHERE RNO = 1;)
GROUP BY Clause to Remove Duplicate
You can use the GROUP BY clause as an alternative to DISTINCT keyword.
For example, consider below Snowflake query to get unique records from a table using GROUP BY.
SELECT col1, col2, col3
FROM SAMPLE_TABLE3
GROUP BY col1, col2, col3;
This method is usually faster on small tables.
Snowflake supports defining and maintaining constraints, but does not enforce them, except for NOT NULL constraints, which are always enforced. Constraints are provided primarily for data modeling purposes and compatibility with other databases, as well as to support client tools that utilize constraints.
No comments:
Post a Comment