8.2 Table Structure and Schema

Automatic numbering does not eliminate all duplicates: the same external device can still be inserted twice with different numbers. Schema design starts by distinguishing row identifiers from keys that prevent business duplicates.

Internal Identifiers and Business Keys

This example manages an internal number separately from an external device code.

CREATE TRANSACTION TABLE ch8_schema (
    id            LONG PRIMARY KEY AUTO_INCREMENT,
    external_code VARCHAR(64) NOT NULL,
    device_name   VARCHAR(128) NOT NULL,
    price         DECIMAL(18,2),
    state         JSON,
    updated_at    DATETIME
);
CREATE UNIQUE INDEX ch8_schema_code ON ch8_schema(external_code);

INSERT INTO ch8_schema(external_code, device_name, price, state, updated_at)
VALUES ('ERP-01', 'Pump A', 19900.25, '{"status":"NORMAL"}',
        TO_DATE('2026-01-01 10:00:00', 'YYYY-MM-DD HH24:MI:SS'));

SELECT external_code, device_name, price, state->'$.status' AS status
  FROM ch8_schema;

One ERP-01 row is returned with price 19900.25 and state NORMAL. The server assigns id. Do not make consecutive or gap-free numbering a business requirement. For retrieving assigned IDs, check your SDK and AUTO_INCREMENT.

PRIMARY KEY and UNIQUE

A TRANSACTION table permits one single-column PRIMARY KEY. Specify PRIMARY KEY after the column definition, or add it to an existing table with CREATE PRIMARY KEY INDEX. It cannot be created on data containing NULL or duplicate values.

Use a composite UNIQUE INDEX to enforce uniqueness across multiple columns. Do not copy UNIQUE, FOREIGN KEY, or table-level PRIMARY KEY syntax inside CREATE TABLE from another DBMS. Define uniqueness with CREATE UNIQUE INDEX after table creation.

UNIQUE INDEX keys containing NULL are not considered duplicates of other NULL-containing keys. If a code must exist and be unique, also declare NOT NULL as in the example. Empty strings are another potential pitfall. Check Machbase empty-string/NULL behavior through the actual input path and validate required codes during collection.

The following optional exercise checks a UNIQUE violation. Run it separately from successful inserts.

-- Expected failure: duplicate external_code
INSERT INTO ch8_schema(external_code, device_name)
VALUES ('ERP-01', 'Duplicate Pump');

After failure, ERP-01 should still have one row. To update on duplicates, use the separate UPSERT rules.

Choosing Data Types

Check complete ranges in the Data Type Dictionary and monetary values in DECIMAL. Do not use DOUBLE for every monetary column simply because an example does.

Constraints and Input Validation

TRANSACTION requires at least one user column. LOG automatic arrival timestamps and TAG METADATA/BASETIME/BASEDISTANCE are unavailable. Do not assume foreign keys automatically enforce referential integrity; include required relationship validation in the application and data checks.

The example UNIQUE INDEX does not validate business rules for device names or prices. Define required values, allowed states, and quantity ranges separately.

SELECT COUNT(*) AS device_count FROM ch8_schema;
DROP TABLE ch8_schema;

The count before cleanup is 1. Continue with Create, Alter, and Drop for schema changes and Index Design for query access paths.