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?
Column Level vs Table Level Constraints
Moderator: NorbertKrupa
Column Level vs Table Level Constraints
THANKS - BECKSTER
-
- GURU
- Posts: 527
- Joined: Tue Oct 22, 2013 9:36 pm
- Location: Chicago, IL
- Contact:
Re: Column Level vs Table Level Constraints
Do you have an example?becky wrote: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.
In the mean time, take a look at adding constraints which covers column & table constraints.becky wrote:Does that concept exist in Vertica? Or are all of the constraint types (PK, FK, NOT NULL and DEFAULT) at the column level only?
Checkout vertica.tips for more Vertica resources.
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Column Level vs Table Level Constraints
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.
The TABLE_CONSTRAINTS system table only includes the PK, UK and FK:
The CONSTRAINT_COLUMNS system table includes PK, UK, FK and NOT NULL constraints:
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.
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=> 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
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)
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)
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)
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)
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: Column Level vs Table Level Constraints
Thanks norbertk and Jim! This is what I needed.
THANKS - BECKSTER
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Column Level vs Table Level Constraints
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
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
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.