Creating and Altering Columns

Description

BLOB/CLOB type columns can be created/added/deleted by using a CREATE TABLE statement or an ALTER TABLE statement.

Note
Example

-- creating a table and CLOB column

CREATE TABLE doc_t (doc_id VARCHAR(64) PRIMARY KEY, content CLOB);

 

-- an error occurs when UNIQUE constraint is defined on CLOB column

ALTER TABLE doc_t ADD CONSTRAINT content_unique UNIQUE(content);

 

-- an error occurs when creating an index on CLOB column

CREATE INDEX ON doc_t (content);

 

-- creating a table and BLOB column

CREATE TABLE image_t (image_id VARCHAR(36) PRIMARY KEY, doc_id VARCHAR(64) NOT NULL, image BLOB);

 

-- an error occurs when adding a BOLB column with NOT NULL constraint

ALTER TABLE image_t ADD COLUMN thumbnail BLOB NOT NULL;

 

-- an error occurs when adding a BLOB column with DEFAULT attribute

ALTER TABLE image_t ADD COLUMN thumbnail2 BLOB DEFAULT BIT_TO_BLOB(X'010101');