Create Primary key in vertica table

Creating a table with primary key (System assigns name C_PRIMARY):

DROP TABLE IF EXISTS sales;

CREATE TABLE sales (

sales_id integer primary key ,

code varchar(20),

quantity smallint,

amount money,

product_id int,

sales_date datetime);

INSERT INTO sales

SELECT 1,'RT0132',1,100,10023,now()

Creating a primary on an existing table:

DROP TABLE IF EXISTS product;

create table product(

product_id int ,

description  varchar(100) ,

date_added datetime);

ALTER TABLE product ADD PRIMARY KEY (product_id) ;

Informational Message =>

Code: 2623 SQL State: 23502 --- Column "product_id" definition changed to NOT NULL

Check the constraint information from System table:

Select * from table_constraints where table_name = 'sales';

Dropping Primary Key Constraint:

ALTER TABLE sales DROP CONSTRAINT C_PRIMARY;

Creating a named Primary key:

DROP TABLE IF EXISTS sales ;

CREATE TABLE sales (

sales_id int CONSTRAINT pk_sales PRIMARY KEY ,

code varchar(20),

quantity smallint,

amount money,

product_id int,

sales_date datetime);

Creating a named Primary key inline table creation:

DROP TABLE IF EXISTS sales ;

Create table sales (

sales_id int,

code varchar(20),

quantity smallint,

amount money,

product_id int,

sales_date datetime,

CONSTRAINT pk_sales PRIMARY KEY(sales_id)

);

Creating composite primary key with multiple columns:

DROP TABLE IF EXISTS sales ;

Create table sales (

sales_id int,

code varchar(20),

quantity smallint,

amount money,

product_id int,

sales_date datetime,

CONSTRAINT pk_sales PRIMARY KEY(sales_id,code)

);

Checking column level constraints:

select tc.constraint_name,tc.constraint_type,cc.column_name from

table_constraints  TC JOIN

constraint_columns CC ON CC.constraint_id = tc.constraint_id

WHERE tc.table_name ='sales'