CODEWITHSUNDEEP
sqlpostgresql
J3Y
J3Y

Reputation: 1853

Constraint on a related table

I have 2 tables (ItemType and Item). ItemType has a boolean hasSize, and the Item table has a size column.

I'd like to enforce that size is only set if the hasSize boolean = true.

I've seen solutions discussed which use triggers for validation. However, that doesn't provide the same robustness as a constraint.

Is this a problem of non-ideal schema design, or are there appropriate solutions for this?

Upvotes: 3

Views: 80

Answers (2)

Haleemur Ali
Haleemur Ali

Reputation: 28313

Check constraints cannot refer to other tables in the database & a trigger is a valid strategy around this limitation. I don't think your schema design is bad. Certain types of items are sizeable, and that information belong in the item_type table.

Here is an alternate solution not using triggers, where I impose a check constraint on an UPDATABLE VIEW to prevent data modification in the underlying tables, using the WITH CHECK OPTION modifier. note that this will only validate if data inserted or updated through the view. You must grant appropriate permissions so that the application role cannot modify the item table directly, but can through the UPDATABLE VIEW

Here's a self-contained script with sample data & examples insert that fails & insert that succeeds

-- set up dummy `item_type` table
CREATE TABLE item_type (
id int primary key generated by default as identity,
has_size bool,
name text not null unique
);
INSERT INTO item_type 
  (has_size, name) 
VALUES 
  (true, 'sizable type')
, (false, 'unisize');

-- set up dummy `item` table
CREATE TABLE item (
id int primary key generated by default as identity,
type_id int references item_type(id),
name text NOT NULL,
size text
);

INSERT INTO item
  (type_id, name, size)
VALUES
  (1, 'worlds best dad tee shirt', 'M')
, (2, 'usb key', NULL);

-- create updatable check view.
CREATE VIEW item_view AS
SELECT item.*
FROM item 
WHERE EXISTS (
SELECT 
FROM item_type 
WHERE item.type_id = item_type.id
  AND (item.size IS NOT NULL) = item_type.has_size
) 
WITH CASCADED CHECK OPTION; 
--    ^^^
-- i could have alternately used local 
-- instead of cascaded

This insert will fail

INSERT INTO item_view 
  (type_id, name, size) 
VALUES
  (2, 'dad socks', 'M');

It generates the following error:

ERROR:  new row violates check option for view "item_view"
DETAIL:  Failing row contains (3, 2, dad socks, M).

This insert will succeed

INSERT INTO item_view 
  (type_id, name, size) 
VALUES
  (2, 'dad socks', NULL);

SELECT * FROM item returns the following result as expected

 id | type_id |           name            | size
----+---------+---------------------------+------
  1 |       1 | worlds best dad tee shirt | M
  2 |       2 | usb key                   |
  4 |       2 | dad socks                 |

Upvotes: 3

Laurenz Albe
Laurenz Albe

Reputation: 248165

Yes, I'd call this a problem of the design. Normally such problems can be avoided by not storing hassize, since it can be derived when the database is queried.

Sometimes it is necessary to store redundant data to improve performance, but then you have to pay the price of potential inconsistency that you are afraid of.

If you cannot avoid persisting hassize, using a trigger is usually the best solution; try to avoid anomalies by reducing concurrency (e.g. locks or serializable isolation).

Upvotes: 2

Related Questions