Enabling Primary Key Enforement in v7.2 - disposition of existing data

PDS

Reading through the New Features documentation I found the new option to enforce Primary Keys (finally!).  I went out to the full documentation and read up on the scenarios for enforcement, but one i could not find.  Does anyone know what happens if you have data in the table and then turn on enforcement?  Meaning, does the existing Primary Key data in the table get validated to ensure that it is good (this is what many other dbms's do)?  Or does it assume that it is good and only validate new data that comes in? 

If it does not validate existing data when you turn it on, I would assume best practice be to enable it for a table, then immediately run 'analyze_constraints()' afterwards (?)     Would we have any concerns if you turn it on, do not validate, and a report runs that encounters duplicates? - I ask this since there was a note that queries would run faster if the optimizer knew there was no duplicates.

knicely87

Hi,

 

Vertica does check the existing data prior to enabling primary key enforcement. Here is a simple example:

 

dbadmin=> CREATE TABLE test (col1 INT NOT NULL PRIMARY KEY, col2 INT);
CREATE TABLE
dbadmin=> INSERT INTO test VALUES (1, 1);
 OUTPUT
--------
      1
(1 row)

dbadmin=> INSERT INTO test VALUES (1, 2);
 OUTPUT
--------
      1
(1 row)

dbadmin=> ALTER TABLE test ALTER CONSTRAINT C_PRIMARY ENABLED;
ERROR 6745:  Duplicate key values: 'col1=1' -- violates constraint 'public.test.C_PRIMARY'
dbadmin=> SELECT * FROM primary_keys limit 1;
   constraint_id   | constraint_name | column_name | ordinal_position | table_name | constraint_type | is_enabled | table_schema
-------------------+-----------------+-------------+------------------+------------+-----------------+------------+--------------
 45035996273791192 | C_PRIMARY       | col1        |                1 | test       | p               | f          | public
(1 row)

dbadmin=> DELETE FROM test WHERE col2 = 2;
 OUTPUT
--------
      1
(1 row)

dbadmin=> ALTER TABLE test ALTER CONSTRAINT C_PRIMARY ENABLED;
ALTER TABLE
dbadmin=> SELECT is_enabled FROM primary_keys WHERE constraint_name = 'C_PRIMARY';
 is_enabled
------------
 t
(1 row)

dbadmin=> INSERT INTO test VALUES (1, 2);
ERROR 6745:  Duplicate key values: 'col1=1' -- violates constraint 'public.test.C_PRIMARY'

Thanks!

PDS

Great thanks.   Now if we can just get it (or analyze_constraints) to give the option to *remove* duplicates