Skip to main content

CREATE BLOCKCHAIN (bSQL) | Blockpoint Docs

Creates a new blockchain in a given database. For examples, see Examples.

bSQL Syntax Conventions

Simple Syntax​

-- Simple CREATE BLOCKCHAIN Syntax (common if not using options)
CREATE BLOCKCHAIN
    { database_name.blockchain_name | blockchain_name },
    <blockchain_type> [WITHOUT STATS] 
    ( { <column_definition> } [ ,...n ] )
    [ INDEX { <index_definition> [ ,...n ] }]

Full Syntax​

CREATE BLOCKCHAIN 
    { database_name.blockchain_name | blockchain_name }
    <blockchain_type> [WITHOUT STATS] 
    ( {   <column_definition> [ ,... n ] )
    [ INDEX { <index_definition> [ ,...n ] }]

<blockchain_type> ::=
[ HISTORICAL | HISTORICAL + | TRADITIONAL | SPARSE ]
  
<column_definition> ::=
{column_name <data_type>} | {TIMESTAMP}
    [ PACKED [= <bool>] | PADDED [= <bool>]]
    [ NULLABLE [= <bool>] ]
    [ DEFAULT [ VALUE ] = default_value ]
    [ ENCRYPT = "key_name" ]
    [ TRACK VALIDITY = '['"key_name" , validity_level']']
    [ <column_constraint> [, ...n ] ]
    [ CHECK '['logical_expression']']
    [ COMPUTED | AS '['computed_expression']']
  
<data_type> ::=
type_name [ SIZE = <byte_length> ]
  

<column_constraint> ::=
{     { PRIMARY KEY | UNIQUE }
        [ AUTO INCREMENT ['['seed, increment']'] ]
  
  | [ FOREIGN KEY ]
       '[' databse_name.blockchain_reference | blockchain_reference column_reference ']'
        [ ON UPDATE '{' NO ACTION | CASCADE | SET NULL | SET DEFAULT '}' ]
        [ ON DELETE '{' NO ACTION | CASCADE | SET NULL | SET DEFAULT '}' ] 
  
  | [ HISTORICAL REFERENCE ]
        '[' databse_name.blockchain_reference | blockchain_reference column_reference ']'
}
  
<index_definition> ::=
 "index_name" index_type (index_column_name)

Arguments​

database_name
Is the name of the database in which the blockchain is created. database_name must specify the name of an existing database. If not specified, database_name defaults to the current database.

Blockchain​

blockchain_name
Is the lowercase name of the new blockchain and can be a maximum of 128 characters and can't include any spaces or numbers.

blockchain_type
The type of blockchain to be created. Blockchain type defaults to Historical. For more information on blockchain types see blockchain overview.

WITHOUT STATS Specifies that the blockchain will not be built with statistics, for more information on blockchain stats see stats overview.

Schema​

column_name The reference name for the column, it must be unique within a blockchain and can't contain any spaces.

data_type The type of data for this column to store, for a full list of data types and there limitations see system data types.

TIMESTAMP The timestamp keyword generates a column definition with the name "timestamp" whose values are stored as the current time when the record was inserted.

PACKED Specifies that the values inserted into this column don't span there default size instead they are packed and pointers to there start and end are stored in a footer.

PADDED Specifies that the values inserted into this column span there default size and no footer exists.

NULLABLE Specifies that when a record is inserted into the blockchain, if there is no value that corresponds to the nullable column, the null value is provided.

Nullability rules within a blockchain definition​

  • Any data type that allows packed values, must be packed in order for the value to be nullable.
  • When column nullability is not explicitly specified, it defaults to false.

DEFAULT
Specifies the value provided for the column when a value is not explicitly supplied during an insert.

default_value
Specifies the value to be set as the default, this value should be compatible with the data type specified for its corresponding column.

DEFAULT definitions​

  • A column can have only one DEFAULT definition.
  • A DEFAULT definition can only contain constant values.

Encryption​

ENCRYPT Dictates that a column will be encrypted.

key_name
The name of the encryption key used to encrypt this column.

TRACK VALIDITY
Dictates that the column will check for corrupted data.

key_name
The name of the encryption key used to track the validity of the column.

validity_level
The name of the encryption key used to track the validity of the column.

Validity Levels​

  • LEVEL ONE: A smaller validity tracking method.
  • LEVEL TWO: A larger validity tracking method.

Constraints​

UNIQUE​

UNIQUE Is a constraint that asserts uniqueness for a specified column through a unique index and allows null values. A blockchain can have multiple UNIQUE constraints.

CHECK​

CHECK Dictates that this column will enforce a blockchain-specific logical expression.

logical_expression
The search condition.

NOTE: The search condition must evaluate to a Boolean expression and cannot reference another table.

PRIMARY KEY​

PRIMARY Dictates that this column will have enforced uniqueness and can be referenced by a foreign key. Multiple primary keys can't be specified in a single schema.

Primary Key Constraints:​

  • When a primary key is discontinued from the blockchain it must not be referenced by a foreign key, or an error is produced.
  • If the column has been specified as CASCADE, the discontinued key will be removed from all blockchains that reference that column, if that key exists.
  • Primary keys automatically generate an index that enables the system to track the state of a record and its interactions with other data stores.
  • Primary keys can't be defined in a HISTORICAL blockchain, for a similar functionality try adding an ALT1 index on the column instead.

HISTORICAL REFERENCE KEY​

HISTORICAL REFERENCE Dictates that values in this column references a primary key upon insertion.

Historical Reference Constraints:​

  • A historical reference key must reference a PRIMARY KEY column in another blockchain.
  • When a value is inserted into a reference key column, the primary key must exist in the reference blockchain.
  • Reference key constraints are not enforced on temporary blockchains.
  • Foreign key constraints can reference only columns in a PRIMARY KEY.
  • Columns participating in a historical reference relationship must be defined with the same type and size.
  • Unlike foreign keys, historical reference keys can be defined in a historical blockchain, as they only enforce the constraint upon insertions.

FOREIGN KEY​

FOREIGN KEY Provides a reference to another tables primary key.

Foreign Key Constraints:​

  • A foreign key must reference a PRIMARY KEY column in another blockchain.
  • When a value is inserted into a foreign key column, the primary key must exist in the reference blockchain.
  • If the column has been specified as CASCADE, the inserted key will be inserted into the reference column as well, if the rest of the schema has a DEFAULT VALUE or is NULLABLE.
  • Foreign key constraints are not enforced on temporary blockchains.
  • Foreign key constraints can reference only columns in a PRIMARY KEY.
  • Columns participating in a foreign key relationship must be defined with the same type and size.
  • Foreign keys can't be defined in a historical blockchain.

blockchain_reference The name of the blockchain in which the primary key to reference exists.

column_reference The name of the column to reference within the specified blockchain.

ON DELETE​

ON DELETE Specifies what action happens to rows in the table created, if those rows have a referential relationship and the referenced row is deleted from the parent table. The default is NO ACTION.

NO ACTION Raises an error and the delete action on the row in the parent blockchain is rolled back.

CASCADE Corresponding rows are deleted from the referencing blockchain if that row is deleted from the parent table.

SET NULL All the values that make up the foreign key are set to NULL if the corresponding row in the parent blockchain is deleted. For this constraint to execute, the foreign key columns must be nullable.

SET DEFAULT All the values that make up the foreign key are set to their default values if the corresponding row in the parent blockchain is deleted. For this constraint to execute, all foreign key columns must have default definitions. If a column is nullable, and there is no explicit default value set, NULL becomes the implicit default value of the column.

UPDATE​

ON UPDATE Specifies what action happens to rows in the blockchain altered when those rows have a referential relationship and the referenced row is updated in the parent table. The default is NO ACTION.

NO ACTION Raises an error, and the update action on the row in the parent blockchain is rolled back.

CASCADE Update cascade is NOT allowed in the system.

SET NULL All the values that make up the foreign key are set to NULL when the corresponding row in the parent blockchain is updated. For this constraint to execute, the foreign key columns must be nullable.

SET DEFAULT All the values that make up the foreign key are set to their default values when the corresponding row in the parent blockchain is updated. For this constraint to execute, all foreign key columns must have default definitions. If a column is nullable, and there is no explicit default value set, NULL becomes the implicit default value of the column.

Computed​

AUTO INCREMENT​

AUTO INCREMENT
Specifies that the column should increment by an increment value every with every insertion. The default seed value is 0 and increment 1.

seed
The starting value designated to be incremented from.

increment
The value added to the previous value to generate the new value.

NOTE: Auto incremented columns are only compatible with unsigned integers. See system data types for reference.

COMPUTED​

COMPUTED | AS
Specifies that the column will not be stored on disk, instead it will only be generated upon output using the computation expression specified.

Computed Constraints:​

  • A computed column can't be the target of mutation.
  • A computed column can't be used as a key in an index.
  • Computed expressions can't include columns that aren't in the blockchain container it's created in.
  • The computed expression must be compatible with the data type specified. If the COMPUTED data type isn't specified the computation is casted to the largest compatible type.

computed_expression
The mathematical expression to be used to generate column values.

Index​

INDEX Specifies that user defined indices will be stored on the column(s). For a more comprehensive explanation of indexes see the index overview.

index_name The name of the index.

index_type The type of index to build.

Index Types:​

  • ALT1: Enforces uniqueness and uses the column key to find the record referenced at that key.
  • ALT2: Enforces uniqueness and uses the column key to find the record ID associated with the key.
  • ALT3: Enforces uniqueness and uses the column key to find all records that correspond to that key.
  • ALT3RID: Enforces uniqueness and uses the column key to find all record IDs that correspond to that key.

index_column_name
Specifies the names of the column in which to build the index on.

Permissions​

MANAGEMENT permissions required on the database.

MANAGEMENT permissions on the database default to members of the admin fixed database role. Members of the admin role can transfer permissions to other users.

Additional Constraint information​

  • A column that has an index created on it cannot be altered in an ALTER BLOCKCHAIN statement unless that index is dropped and then rebuilt.
  • When a constraint is violated in an INSERT, AMEND, or DISCONNECT statement, the statement is ended and all uncommitted statements are rolled back.

Examples​

A. Create a PRIMARY KEY constraint on a column​

The following example shows the column definition for a PRIMARY KEY constraint on the symbol column of the current_price blockchain.

CREATE BLOCKCHAIN financial.current_price TRADITIONAL
 (symbol STRING PACKED PRIMARY, price FLOAT32)

B. Using FOREIGN KEY constraints​

A FOREIGN KEY constraint is used to reference another blockchain. Foreign keys can be single-column keys and must have the same name and column properties as the referenced column. This following example shows a single-column FOREIGN KEY constraint on the current_price blockchain that references the companies blockchain in the DEMO DATABASE.

CREATE BLOCKCHAIN financial.current_price TRADITIONAL 
(id UINT16 PRIMARY AUTO INCREMENT, symbol STRING PACKED FOREIGN [financial, symbol], price FLOAT32)

C. Using UNIQUE constraints​

UNIQUE constraints are used to enforce uniqueness on non-primary key columns. The following example enforces a restriction that the sec_filings column of the reports blockchain must be unique.

CREATE BLOCKCHAIN financial.reports TRADITIONAL 
(symbol STRING PACKED PRIMARY, sec_filings STRING PACKED UNIQUE)

D. Using DEFAULT definitions​

Defaults supply a value when no value is supplied. This example creates a similar blockchain to the example above yet provides a DEFAULT value for the sec_filings column. To see examples of insertions with default values see INSERT.

CREATE BLOCKCHAIN financial.reports TRADITIONAL 
(symbol STRING PACKED PRIMARY, sec_filings STRING PACKED DEFAULT = "report doesn't exist")

F. Complete Blockchain definition.​

The following example shows a more complete blockchain creation using features like DEFAULT, AUTO INCREMENT, PRIMARY, ENCRYPTION, NULLABLE, and INDEX to create the blockchain price_tracking in the DEMO DATABASE.

CREATE BLOCKCHAIN financial.price_tracking
SPARSE (
    id UINT16 ENCRYPT = "price_tracking_key" PRIMARY AUTO INCREMENT,
    symbol STRING PACKED FOREIGN [financial.symbol, symbol],
    sector STRING PACKED DEFAULT = "uncategorized",
    current_price FLOAT32 NULLABLE,
    52_week_high FLOAT32 NULLABLE,
    52_week_low FLOAT32 NULLABLE )
INDEX "price tracker" ALT3(current_price, id)

See Also​