Create and drop foreign key

Vertica doesn't enforce the constraints by default.Here is an example that shows drop foreign key/tables , creating tables, primary key and then adding foreign key relationship to the table.

Drop foreign key:

ALTER TABLE sales  DROP CONSTRAINT IF EXISTS FK_Sales_product;


DROP TABLE IF EXISTS product cascade;
Drop table if exists sales cascade;

create table product(
product_id int NOT NULL ,
description  varchar(100) ,
date_added datetime);

ALTER TABLE product ADD PRIMARY KEY (product_id) ;


Drop table if exists sales;
CREATE TABLE sales (
sales_id integer NOT NULL,
code varchar(20),
quantity smallint,
amount money,
product_id int NOT NULL,
sales_date datetime);

ALTER TABLE sales ADD PRIMARY KEY (sales_id) ;

INSERT INTO sales
select 1,'RC012',1,50,1001,now();


INSERT INTO product
select 1001,'Wagon',now();
 

Vertica 7.1 does not enforce constraint yet. Let us try to violate the primary key constraint. Then detect the constraint violation using analyse_constraints.

Insert into sales
select 1,'RC999',1,50,1001,now();

 12:21:52  [INSERT - 1 row(s), 0.026 secs]  Command processed
... 1 statement(s) executed, 1 row(s) affected, exec/fetch time: 0.026/0.000 sec  [1 successful, 0 warnings, 0 errors]


dbadmin=> SELECT ANALYZE_CONSTRAINTS('sales');
 Schema Name | Table Name | Column Names | Constraint Name | Constraint Type | Column Values
-------------+------------+--------------+-----------------+-----------------+---------------
 public      | sales      | sales_id     | C_PRIMARY       | PRIMARY         | ('1')
(1 row)


 

Creating foreign key:

dbadmin=> ALTER TABLE sales    ADD CONSTRAINT fk_sales_product FOREIGN KEY(product_id)     REFERENCES product(product_id);

WARNING 4887:  Table sales has data. Queries using this table may give wrong results if the data does not satisfy this constraint
HINT:  Use analyze_constraints() to check constraint violation on data
ALTER TABLE

 

Now let us try to violate integrity constraint .

Insert into sales
select 2,'RC999',1,50,1002,now();

It doesnt give any error.


dbadmin=>  SELECT ANALYZE_CONSTRAINTS('sales');
 Schema Name | Table Name | Column Names | Constraint Name  | Constraint Type | Column Values
-------------+------------+--------------+------------------+-----------------+---------------
 public      | sales      | sales_id     | C_PRIMARY        | PRIMARY         | ('1')
 public      | sales      | product_id   | fk_sales_product | FOREIGN         | ('1002')


 

Join of both tables constrainted by PK-FK relationship also doesnt return any error.

dbadmin=>select s.code, p.description from 
sales s join product p on s.product_id=p.product_id
 
 code  | description
-------+-------------
 RC012 | Wagon
 RC999 | Wagon
 RC999 | Wagon
 RC999 | Wagon