NULL constraints are enforced but UNIQUE constraints are not

[beth]

Hello,

Does anyone know why Vertica enforces the NOT NULL constraint, but not the UNIQUE constraint?

Code: Select all

dbadmin=> create table beth (col1 int not null, col2 varchar unique);
CREATE TABLE

dbadmin=> insert into beth (col1) values (null);
ERROR 2502:  Cannot set a NOT NULL column (col1) to a NULL value in INSERT/UPDATE statement

dbadmin=> insert into beth (col1, col2) values (1, 'Beth');
 OUTPUT
--------
      1
(1 row)

dbadmin=> insert into beth (col1, col2) values (2, 'Beth');
 OUTPUT
--------
      1
(1 row)

dbadmin=> select * from beth;
 col1 | col2
------+------
    1 | Beth
    2 | Beth
(2 rows)

I know that we can use the ANALYZE_CONSTRAINTS function to find the violations, but I just curious why the NULL constraints are not handled in a similar fashion.

Code: Select all

dbadmin=> select analyze_constraints('beth');
 Schema Name | Table Name | Column Names | Constraint Name | Constraint Type | Column Values
-------------+------------+--------------+-----------------+-----------------+---------------
 public      | beth       | col2         | C_UNIQUE        | UNIQUE          | ('Beth')
(1 row)

[JimKnicely]

Hi Beth,

My guess would be that it isn't very costly to enforce a NOT NULL constraint because Vertica is only checking a single row column value. Enforcing a UNIQUE constraint on the other hand, requires Vertica to scan the column values for all rows in the table looking for duplicates. When you have billions and billions of records, that can become very costly

[beth]

Yeah, ok, that makes sense. Thanks, Jim!

[Brett]

Hi,

I'm new. So what constraints are enforced? Do I even need to create them?

[JimKnicely]

FYI - PK and FK constraints are enforced on loads/inserts/updates into fact tables of pre-join projections.

You should always create PKs and FKs. They help the Vertica optimizer choose the best plan (I heard this at the HP Big Data Conference)