0
CREATE TABLE IF NOT EXISTS documents (
    d_reg_id VARCHAR(255) NOT NULL,
    d_reg_date DATE NOT NULL,
    d_id varchar(255),
    d_date DATE NOT NULL,
    d_theme VARCHAR(100) NOT NULL,
    d_description VARCHAR(1000),
    d_access VARCHAR(3) DEFAULT 'Нет',
    d_control VARCHAR(3) DEFAULT 'Нет',
    CONSTRAINT pk_reg_id PRIMARY KEY (d_reg_id)
) Engine=InnoDb;

CREATE TABLE IF NOT EXISTS correspondents (
    c_id VARCHAR(255) NOT NULL,
    c_correspondents VARCHAR(3) NOT NULL,
    CONSTRAINT pk_c_id PRIMARY KEY (c_id),
    CONSTRAINT fk_c_id FOREIGN KEY (c_id) REFERENCES documents(d_reg_id)
) Engine=InnoDb;

CREATE TABLE IF NOT EXISTS delivery_types (
    dt_id VARCHAR(255) NOT NULL,
    dt_type VARCHAR(14),
    dt_period_of_execution DATE,
    CONSTRAINT chk_period_of_execution CHECK (delivery_types.dt_period_of_execution > documents.d_reg_date),
    CONSTRAINT pk_dt_id PRIMARY KEY (dt_id),
    CONSTRAINT fk_dt_id FOREIGN KEY (dt_id) REFERENCES documents(d_reg_id)
    ) Engine=InnoDb;

I'm getting problem here, I need to check whether the period of execution cannot be earlier than the date of registration. Thank you in advance!

camerons2001
  • 41
  • 1
  • 9

1 Answers1

0

A check constraint can only check values in a single row -- it can't even refer to other rows in the same table.

For this functionality, you probably need a trigger. You can also use a user-defined function to return the value from the other table.

It is a bit hard to be more precise. A check constraint logically ensures that the data is consistent over all time, not just when the data in the table changes. It is unclear whether this is something that you only want to do when the value is inserted into delivery_types or when the value changes or when the value changes in the other table as well.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Where can I refer to? I've read that I can use functions and triggers? – camerons2001 Jul 12 '20 at 15:03
  • [update-trigger-example](https://www.mysqltutorial.org/mysql-triggers/mysql-after-update-trigger/) might be a starter ... you can create a exception in the trigger => causing a rollback of the current transaction, like the constraint would do. [exception-example](https://stackoverflow.com/questions/24/throw-an-error-in-a-mysql-trigger) – Michael Hauptmann Jul 12 '20 at 16:13