Subscribe to this thread
Home - General / All posts - schema constraint sql update
lionel

519 post(s)
#28-Nov-18 21:47

Hi

is is possible to use constraints as a dynamic computed field .

when add new record the constraint update the column with some value from other the column that have been inserted !!

regard's


join image "Because my dad promised me" interstellar from Manifold: Time by Stephen Baxter. power Math destruction

adamw


8,259 post(s)
#12-Dec-18 08:49

In 9, that's called a computed field, not a constraint.

A constraint is an expression that must evaluate to TRUE for all records in the table, verified when you add new records or change existing records.

Example constraint:

--SQL9

CREATE TABLE t (mfd_id INT64INDEX mfd_id_x BTREE (mfd_id),

  a INT32CONSTRAINT a_positive AS [[ a > 0 ]]);

INSERT INTO t (mfd_id, a) VALUES (1, 1); -- succeeds

INSERT INTO t (mfd_id, a) VALUES (2, -1); -- fails

UPDATE t SET a = -1 WHERE mfd_id = 1; -- fails

UPDATE t SET a = 5 WHERE mfd_id = 1; -- succeeds

Example computed field:

--SQL9

CREATE TABLE u (mfd_id INT64INDEX mfd_id_x BTREE (mfd_id),

  a INT32, a_squared FLOAT64 AS [[ a * a ]]);

INSERT INTO u (mfd_id, a) VALUES (1, 1); -- sets a_squared to 1

INSERT INTO u (mfd_id, a) VALUES (2, 5); -- sets a_squared to 25

UPDATE u SET a = 6 WHERE mfd_id = 1; -- sets a_squared to 36

Hope this helps.

Manifold User Community Use Agreement Copyright (C) 2007-2017 Manifold Software Limited. All rights reserved.