Он я появляется: ОШИБКА: нет ограничения unique, что совпал с колоннами, данными в вышеупомянутой таблице «tb_material»

Кто-то мог бы проверять решения следующих SQL

-- Creación de tablas
BEGIN WORK;
CREATE TABLE manufacturing.tb_warehouse (
        wh_code CHARACTER(5),
        wh_name CHARACTER VARYING(40) NOT NULL,
        wh_address CHARACTER VARYING(120),
        wh_city CHARACTER VARYING(25),
        wh_country CHARACTER VARYING(50) NOT NULL,
        wh_account_no CHARACTER VARYING(40),
        created_by_user CHARACTER VARYING(10) DEFAULT'CV_SYSTEM' NOT NULL,
        created_date DATE,
        updated_date DATE,
        CONSTRAINT pk_tb_warehouse PRIMARY KEY (wh_code)
);

CREATE TABLE manufacturing.tb_transaction_type (
        trans_type_code CHARACTER(5),
        trans_type_desc CHARACTER VARYING(60) NOT NULL,
        created_by_user CHARACTER VARYING(10) DEFAULT'CV_SYSTEM' NOT NULL,
        created_date DATE,
        updated_date DATE,
        CONSTRAINT pk_tb_transaction_type PRIMARY KEY (trans_type_code)
);
CREATE TABLE manufacturing.tb_material (
        material_code CHARACTER(5),
        material_name CHARACTER VARYING(60) NOT NULL,
        material_unit_price NUMERIC (12, 2),
        parent_material_code CHARACTER(5),
        material_type CHARACTER VARYING(20) DEFAULT'DEFAULT' NOT NULL,
        created_by_user CHARACTER VARYING(10) DEFAULT'CV_SYSTEM' NOT NULL,
        created_date DATE,
        updated_date DATE,
        CONSTRAINT pk_tb_material PRIMARY KEY(material_code),
        CONSTRAINT fk_tb_material FOREIGN KEY(parent_material_code) REFERENCES manufacturing.tb_material
);

CREATE TABLE manufacturing.tb_lot (
        lot_number CHARACTER(10),
        lot_status CHARACTER VARYING(15) NOT NULL,
        lot_material_code CHARACTER(5) NOT NULL,
        lot_quantity NUMERIC(10,2) NOT NULL,
        created_by_user CHARACTER VARYING(10) DEFAULT'CV_SYSTEM',
        created_date DATE,
        updated_date DATE,
        CONSTRAINT pk_tb_lot PRIMARY KEY(lot_number),
        CONSTRAINT fk_tb_lot FOREIGN KEY(lot_material_code) REFERENCES manufacturing.tb_material(parent_material_code),
        CONSTRAINT ck_lot_status CHECK(lot_status = 'GOOD''QA TEST''IN TRANSIT''BLOCKED')
);

CREATE TABLE manufacturing.tb_transaction (
        trans_id INTEGER,
        trans_wh_code CHARACTER(5) NOT NULL,
        trans_lot_number CHARACTER(10) NOT NULL,
        trans_type_code CHARACTER(5) NOT NULL,
        trans_process_order CHARACTER VARYING(15),
        trans_quantity NUMERIC(10,2) NOT NULL,
        trans_debit_credit_flag CHARACTER(1) NOT NULL,
        trans_shipping_num CHARACTER VARYING(12),
        created_by_user CHARACTER(10) NOT NULL DEFAULT'CV_SYSTEM',
        created_date DATE,
        updated_date DATE,
        CONSTRAINT pk_trans_id PRIMARY KEY(trans_id),
        CONSTRAINT fk_trans_wh_code FOREIGN KEY(trans_wh_code) REFERENCES tb_warehouse,
        CONSTRAINT fk_trans_lot_number FOREIGN KEY(trans_lot_number) REFERENCES tb_lot,
        CONSTRAINT fk_trans_type_code FOREIGN KEY(trans_type_code) REFERENCES manufacturing.tb_transaction(tb_transaction_type),
        CONSTRAINT ck_trans_debit_credit_flag CHECK(trans_debit_credit = 'D' OR 'C')
);

COMMIT WORK;
0
задан 09.11.2019, 20:27
1 ответ

Ошибка это у тебя есть aquГ-:

CONSTRAINT ck_lot_status CHECK(lot_status = 'GOOD''QA TEST''IN TRANSIT''BLOCKED')"

Я это поместил asГ-:

CONSTRAINT ck_lot_status CHECK(lot_status='GOOD' OR lot_status='QA TEST' OR lot_status='IN TRANSIT' OR lot_status='BLOCKED')
0
ответ дан 01.12.2019, 12:10