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

• You cannot create the index file for a LOB type column.
• You cannot define the PRIMARY KEY, FOREIGN KEY, UNIQUE, and NOT NULL constraints for a LOB type column. However, SHARED property cannot be defined and DEFAULT property can only be defined by the NULL value.
• LOB type column/data cannot be the element of collection type data.
• If you are deleting a record containing a LOB type column, all files located inside a LOB column value (Locator) and the external storage will be deleted. When a record containing a LOB type column is deleted in a basic key table, and a record of a foreign key table that refers to the foregoing details is deleted at the same time, all LOB files located in a LOB column value (Locator) and the external storage will be deleted. However, if the relevant table is deleted by using a DROP TABLE statement, or a LOB column is deleted by using an ALTER TABLE...DROP statement, only a LOB column value (LOB Locator) is deleted, and the LOB files inside the external storage which a LOB column refers to will not be deleted.

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); -- Error

/an error occurs when creating an index on CLOB column

CREATE INDEX ON doc_t (content); -- Error //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; -- Error

//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'); -- Error