15 lines
874 B
SQL
15 lines
874 B
SQL
-- 创建全局资产标识符注册表，保证 ICCID/VirtualNo 跨 tb_iot_card 和 tb_device 全局唯一
|
||
CREATE TABLE IF NOT EXISTS tb_asset_identifier (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
identifier VARCHAR(100) NOT NULL,
|
||
asset_type VARCHAR(20) NOT NULL,
|
||
asset_id BIGINT NOT NULL,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
CONSTRAINT uq_asset_identifier UNIQUE (identifier)
|
||
);
|
||
CREATE INDEX IF NOT EXISTS idx_asset_identifier_asset ON tb_asset_identifier(asset_type, asset_id);
|
||
COMMENT ON TABLE tb_asset_identifier IS '全局资产标识符注册表';
|
||
COMMENT ON COLUMN tb_asset_identifier.identifier IS '标识符值（ICCID 或 VirtualNo，全局唯一）';
|
||
COMMENT ON COLUMN tb_asset_identifier.asset_type IS '资产类型：iot_card 或 device';
|
||
COMMENT ON COLUMN tb_asset_identifier.asset_id IS '对应资产的主键 ID';
|