Column Level vs Table Level Constraints

[becky]

Hi guys,

In most databases there are column level constraints which have scope only to the column it is defined, and table level constraints which can see every column in the table.

Does that concept exist in Vertica? Or are all of the constraint types (PK, FK, NOT NULL and DEFAULT) at the column level only?

[NorbertKrupa]

In most databases there are column level constraints which have scope only to the column it is defined, and table level constraints which can see every column in the table.

Do you have an example?

Does that concept exist in Vertica? Or are all of the constraint types (PK, FK, NOT NULL and DEFAULT) at the column level only?

In the mean time, take a look at adding constraints which covers column & table constraints.

[JimKnicely]

It seems like all constraint types are table constraints except for the NOT NULL constraint which makes sense as a NOT NULL constraint can be on only a single column.

Code: Select all

dbadmin=> create table test1 (col2 int primary key);
CREATE TABLE

dbadmin=> create table test2 (col1 int primary key, col2 int not null references test1 (col2), col3 int unique, col4 INT default 1, col5 identity);
CREATE TABLE

The TABLE_CONSTRAINTS system table only includes the PK, UK and FK:

Code: Select all

dbadmin=> select * from table_constraints where table_name = 'test2';
   constraint_id   | constraint_name | constraint_schema_id | constraint_key_count | foreign_key_count |     table_id      | table_name | foreign_table_id  | constraint_type
-------------------+-----------------+----------------------+----------------------+-------------------+-------------------+------------+-------------------+-----------------
 45035996273757112 | C_PRIMARY       |    45035996273704976 |                    1 |                 0 | 45035996273757110 | test2      |                 0 | p
 45035996273757114 | C_UNIQUE        |    45035996273704976 |                    1 |                 0 | 45035996273757110 | test2      |                 0 | u
 45035996273757116 | C_FOREIGN       |    45035996273704976 |                    1 |                 1 | 45035996273757110 | test2      | 45035996273757106 | f
(3 rows)

The CONSTRAINT_COLUMNS system table includes PK, UK, FK and NOT NULL constraints:

Code: Select all

dbadmin=> select column_name, constraint_name, constraint_type from constraint_columns where table_name = 'test2';
 column_name | constraint_name | constraint_type
-------------+-----------------+-----------------
 col1        | C_NOTNULL       | n
 col1        | C_PRIMARY       | p
 col2        | C_FOREIGN       | f
 col2        | C_NOTNULL       | n
 col3        | C_UNIQUE        | u
 col5        | C_NOTNULL       | n
(6 rows)

It is interesting to note that the DEFAULT and IDENTITY constraints do not appear in either of the CONSTRAINT system tables. So does Vertica not consider these true constraints?

Anyway, you can check the COLUMNS system table for info. on defaults and identities.

Code: Select all

dbadmin=> select column_name, is_nullable, column_default, is_identity from columns where table_name = 'test2';
 column_name | is_nullable | column_default | is_identity
-------------+-------------+----------------+-------------
 col1        | f           |                | f
 col2        | f           |                | f
 col3        | t           |                | f
 col4        | t           | 1              | f
 col5        | f           |                | t
(5 rows)

Although, if you use the EXPORT_OBJECTS() function, it looks like Vertica treats the DEFAULT and IDENTITY constraints as column constraints. Just like the NOT NULL constraint, the DEFAULT and IDENTITY constraints can only be applied to a single column:

Code: Select all

dbadmin=> select export_objects('','test2');
                                                                                                                                                                                                     export_objects                                                                                                                                                                             
--------------------------------------------------------------------------------------------


CREATE TABLE public.test2
(
    col1 int NOT NULL,
    col2 int NOT NULL,
    col3 int,
    col4 int DEFAULT 1,
    col5  IDENTITY
);

ALTER TABLE public.test2 ADD CONSTRAINT C_PRIMARY PRIMARY KEY (col1);
ALTER TABLE public.test2 ADD CONSTRAINT C_UNIQUE UNIQUE (col3);

ALTER TABLE public.test2 ADD CONSTRAINT C_FOREIGN FOREIGN KEY (col2) references public.test1 (col2);


SELECT MARK_DESIGN_KSAFE(1);

(1 row)

[becky]

Thanks norbertk and Jim! This is what I needed.

[JimKnicely]

FYI...

According to the SQL Reference Guide:

A Column-Constraint adds a referential integrity constraint to the metadata of a column:

http://my.vertica.com/docs/7.1.x/HTML/i ... BLE|_____3

A Table-Constraint adds a constraint to the metadata of a table.

http://my.vertica.com/docs/7.1.x/HTML/i ... BLE|_____4