Skip to main content
Question

How to create text index on a flex table which has composite primary key

  • February 6, 2020
  • 4 replies
  • 12 views

rajatpaliwal86
Forum|alt.badge.img+2

dbadmin=> CREATE TEXT INDEX plain_text_search_index ON f_network_events(id, raw) TOKENIZER public.FlexTokenizer(long varbinary);
ROLLBACK 4550: Referenced primary key constraint does not exist

id is simply the identity column. The primary key is the composite of multiple columns but I don't know how to refer them in text index.

4 replies

Jim_Knicely
Forum|alt.badge.img+2
  • Participating Frequently
  • February 6, 2020

The requirements for a text index are:

  • Requires there be a column with a unique identifier set as the primary key.
  • The source table must have an associated projection, and must be both sorted and segmented by the primary key.

See:
https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/SQLReferenceManual/Statements/CREATETEXTINDEX.htm


rajatpaliwal86
Forum|alt.badge.img+2
  • Author
  • Participating Frequently
  • February 6, 2020

@Jim_Knicely said:
The requirements for a text index are:

  • Requires there be a column with a unique identifier set as the primary key.
  • The source table must have an associated projection, and must be both sorted and segmented by the primary key.

See:
https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/SQLReferenceManual/Statements/CREATETEXTINDEX.htm

Yes, but unfortunately the primary key of the flex table is composed of multiple keys.
create flex table if not exists athena_test.f_network_events
(
"id" IDENTITY(1,1),
"sensor_id" varchar(100) NOT NULL,
"component_id" int NOT NULL,
"flow_id" bigint NOT NULL,
..
..
CONSTRAINT pk PRIMARY KEY ("sensor_id", "component_id", "flow_id") ENABLED
);

How can I create text index on this flex table? Any help would be appreciated.


Bryan_H
Forum|alt.badge.img+2
  • Participating Frequently
  • February 6, 2020

rajatpaliwal86
Forum|alt.badge.img+2
  • Author
  • Participating Frequently
  • February 7, 2020

Yes, but the issue is that it restricts the primary key of a single column only, however my flex table has a primary key composited of multiple columns.