Greenplum Constraints:Table and Column Constraints

  • Post author:
  • Post last modified:February 28, 2018
  • Post category:Greenplum
  • Reading time:5 mins read

Greenplum Constraints are used to apply business rules for the database tables. You can define constraints on columns and tables to restrict the data in your tables.

Greenplum Constraints

Greenplum Database support for constraints is the same as PostgreSQL with some limitations.

Read:

Greenplum constraints includes:

  • CHECK
  • NOT NULL
  • UNIQUE
  • PRIMARY KEY
  • FOREIGN KEY

CHECK Greenplum Constraints and Example

CHECK Greenplum Constraints allows you to specify that the value in a certain column must satisfy a Boolean expression. The boolean condition will evaluate to either true or false. You can write CHECK constraint as table or column level.

For example, if you want to check the positive marks of the student you can use CHECK constraint:

CREATE TABLE Students_marks 
 ( Student_ID integer, 
 name text, 
 Marks integer CHECK (Marks >= 0) );

Greenplum Database will throw error when you try to insert negative marks.

training=> insert into Students_marks values (1,'ABC',50); 
INSERT 0 1 
training=> insert into Students_marks values (2,'BCD',65); 
INSERT 0 1 
training=> insert into Students_marks values (2,'BCD',0); 
INSERT 0 1 
training=> insert into Students_marks values (2,'BCD',-10); 
ERROR: One or more assertions failed (seg1 gpdb-training.localdomain:40001 pid=128218) 
DETAIL: Check constraint students_marks_marks_check for table students_marks was violated 
training=>

NOT NULL Greenplum Constraints and Example

Not-null constraints specify that a column must not contain the null value in it. Constrint will thow error if you enter NULL values. A not-null constraint is always written as a column constraint.

For example, there cannot be any null marks associated with student result. You can specify the NOT NULL constraint on the marks.

CREATE TABLE Students_marks 
 ( Student_ID integer, 
 name text, 
 Marks integer NOT NULL );

NOT NULL Greenplum Constraints will thow error if you insert NULL values to column which is soecified as NOT NULL.

training=> insert into Students_marks values (1,'ABC',50); 
INSERT 0 1 
training=> insert into Students_marks values (2,'BCD',65); 
INSERT 0 1 
training=> insert into Students_marks values (2,'BCD',NULL); 
ERROR: One or more assertions failed (seg1 gpdb-training.localdomain:40001 pid=133441) 
DETAIL: Not null constraint for column marks of table students_marks was violated 
training=>

UNIQUE Greenplum Constraints and Example

Unique constraints ensure that the data available in a column or a group of columns is unique with respect to all the rows inserted in the table. In the unique constraint, single null value is allowed.

If you specify the UNIQUE constraint, you must distribute rows on that column itself. Random distribution is not allowed.

For example, Students ID should be unique across all the rows available in Students_marks table.

CREATE TABLE Students_marks 
 ( Student_ID integer UNIQUE, 
 name text, 
 Marks integer);

Greenplum Database will throw error if you try to insert same student records twice.

training=> insert into Students_marks values (1,'ABC',50); 
INSERT 0 1 
training=> insert into Students_marks values (2,'BCD',65); 
INSERT 0 1 
training=> insert into Students_marks values (2,'BCD',NULL); 
INSERT 0 1 
training=> insert into Students_marks values (2,'BCD',65); 
ERROR: duplicate key value violates unique constraint "students_marks_student_id_key" (seg1 gpdb-training.localdomain:40001 pid=140159) 
DETAIL: Key (student_id)=(2) already exists. 
training=>

PRIMARY KEY Greenplum Constraints and Example

PRIMARY KEY Greenplum constraint is combination of UNIQUE and NOT NULL constraint. Like in UNIQUE constraint table, table must be hash-distributed and column should be same as primary key column.

For example, Students ID should be unique and not null across all the rows available in Students_marks table.

CREATE TABLE Students_marks 
 ( Student_ID integer PRIMARY KEY, 
 name text, 
 Marks integer);

Greenplum Database will throw error if you try to insert same student records twice.

training=> insert into Students_marks values (1,'ABC',50); 
INSERT 0 1 
training=> insert into Students_marks values (2,'BCD',65); 
INSERT 0 1 
training=> insert into Students_marks values (2,'BCD',65); 
ERROR: duplicate key value violates unique constraint "students_marks_pkey" (seg1 gpdb-training.localdomain:40001 pid=143998) 
DETAIL: Key (student_id)=(2) already exists. 
training=> insert into Students_marks values (2,'BCD',NULL); 
ERROR: duplicate key value violates unique constraint "students_marks_pkey" (seg1 gpdb-training.localdomain:40001 pid=144635) 
DETAIL: Key (student_id)=(2) already exists. 
training=>

FOREIGN KEY Greenplum Constraints and Example

Foreign keys are not supported in Greenplum. You can declare them, but referential integrity is not enforced.

Foreign key constraints specify that the values in a column or a group of columns must match the values appearing in some row of another table to maintain referential integrity between two related tables.

Referential integrity checks cannot be enforced between the distributed table segments of a Greenplum database.

Tags: , ,