Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.3.1 |  CUBRID 8.4.0 |  CUBRID 8.4.1 |  CUBRID 8.4.3 |  CUBRID 9.0.0 | 

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.

Remark
  • 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.
  • 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.
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');