Interaction with DEFAULT expressions

CREATE TABLE t1 (
  i int,
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  d DATE DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
insert into t1(i) values (1), (2), (3);
update t1 set i = 10 where i = 1;
update t1 set (i, ts, d) = (select i, ts, d from t1 where i = 2) where i = 2;

Interaction with backfills: Backfills over the row (e.g. ADD COLUMN) do not trigger an ON UPDATE change.

vy@free-tier9.gcp-asia-southeast1.crdb.io:26257/defaultdb> CREATE TABLE inventories (
    product_id        INT,
    warehouse_id      INT,
    quantity_on_hand  INT DEFAULT 100 ON UPDATE 50,
    PRIMARY KEY (product_id, warehouse_id)
  );
INSERT INTO inventories (product_id, warehouse_id, quantity_on_hand) VALUES (1, 1, 1);
vy@free-tier9.gcp-asia-southeast1.crdb.io:26257/defaultdb> insert into inventories (product_id, warehouse_id) values (2, 2);
vy@free-tier9.gcp-asia-southeast1.crdb.io:26257/defaultdb> select * from inventories;
  product_id | warehouse_id | quantity_on_hand
-------------+--------------+-------------------
           1 |            1 |                1
           2 |            2 |              100
vy@free-tier9.gcp-asia-southeast1.crdb.io:26257/defaultdb> ALTER TABLE inventories ADD COLUMN new_col INT DEFAULT 13;
vy@free-tier9.gcp-asia-southeast1.crdb.io:26257/defaultdb> select * from inventories;
  product_id | warehouse_id | quantity_on_hand | new_col
-------------+--------------+------------------+----------
           1 |            1 |                1 |      13
           2 |            2 |              100 |      13

Interaction with foreign key updates

vy@free-tier9.gcp-asia-southeast1.crdb.io:26257/defaultdb> CREATE TABLE orders_2 (
    id INT PRIMARY KEY,
    customer_id INT ON UPDATE 10 REFERENCES customers_2(id) ON UPDATE CASCADE ON DELETE CASCADE
  );
ERROR: cannot specify both ON UPDATE expression and a foreign key ON UPDATE action for column with ID 2
SQLSTATE: 42P16

sql: test ON UPDATE interactions #69058

Description

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions