Bug in NOT NULL constraint naming ?

Moderator: NorbertKrupa

Post Reply
User avatar
piglet
Beginner
Beginner
Posts: 45
Joined: Tue Feb 07, 2012 4:04 pm

Bug in NOT NULL constraint naming ?

Post by piglet » Thu Mar 12, 2015 10:05 pm

Hello all,

Is there some sort of bug in the naming of not null constraints in Vertica? If I create a table with 5 columns making them all not null, the constraint name for each column is the same. I would expect 5 different names.

Code: Select all

dbadmin=> create table tab1 (a int not null, b int not null, c int not null, d int not null, e int not null);
CREATE TABLE
dbadmin=> select column_name, constraint_name, constraint_type from v_catalog.constraint_columns where table_name = 'tab1';
 column_name | constraint_name | constraint_type
-------------+-----------------+-----------------
 a           | C_NOTNULL       | n
 b           | C_NOTNULL       | n
 c           | C_NOTNULL       | n
 d           | C_NOTNULL       | n
 e           | C_NOTNULL       | n
(5 rows)
I wonder if this is some sort of internal trick used by Vertica where somehow the columns are group together in some manner for oprimaization?

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Bug in NOT NULL constraint naming ?

Post by JimKnicely » Thu Mar 12, 2015 10:23 pm

Hi Piglet,

Good question! What is really weird is that all NOT NULL constraints have the same ID and NAME, even if they are from different tables!

Code: Select all

dbadmin=> create table tab1 (col1 int not null);
CREATE TABLE
dbadmin=> create table tab2 (col1 int not null);
CREATE TABLE
dbadmin=> create table tab3 (col1 int not null);
CREATE TABLE
dbadmin=> create table tab4 (col1 int not null);
CREATE TABLE

dbadmin=> select table_name, column_name, constraint_id, constraint_name from constraint_columns where table_name ilike 'tab_';
 table_name | column_name | constraint_id | constraint_name
------------+-------------+---------------+-----------------
 tab1       | col1        |             0 | C_NOTNULL
 tab2       | col1        |             0 | C_NOTNULL
 tab3       | col1        |             0 | C_NOTNULL
 tab4       | col1        |             0 | C_NOTNULL
(4 rows)
I don't think Vertica checks the CONSTRAINT_COLUMNS table when enforcing the NOT NULL constraint. I think is is using the IS_NULLABLE flag in the COLUMNS table:

Code: Select all

dbadmin=> select table_name, column_name, is_nullable from columns where table_name ilike 'tab_';
 table_name | column_name | is_nullable
------------+-------------+-------------
 tab1       | col1        | f
 tab2       | col1        | f
 tab3       | col1        | f
 tab4       | col1        | f
(4 rows)
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “New to Vertica Database Administration”