Creating and Altering Columns
BLOB/CLOB type columns can be created/added/deleted by using a CREATE TABLE statement or an ALTER TABLE statement.
- 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 once, 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.
-- 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');