New answers tagged constraint
3
IN MySQL 8 (>= 8.0.16) You can use a CHECK CONSTRAINT
In older Versions you will need an INSERT/UPDATE TRIGGER
That said, you should also normalize your table and use a lookup (or reference/helper) table for an Origin id and a Type id.
CREATE TABLE Product
(
P INT
, PType varchar(19)
, PName varchar(20)
, Price DECIMAL(8,2)
, Origin ...
0
We can save the number of user photos in the user table or a table like user_statistics and use triggers to perform atomic increment and decrement that locks one row (user row) and is safe against concurrent requests:
CREATE TABLE public.user_statistics
(
user_id text NOT NULL,
photo_count smallint NOT NULL DEFAULT 0,
CONSTRAINT ...
2
I should think a table-level CHECK constraint would serve here, something like
CONSTRAINT c1_c2_null_match
CHECK ((c1 IS NULL AND c2 IS NULL)
OR (c1 IS NOT NULL AND c2 IS NOT NULL))
See the MySQL 8.0 documentation.
You could instead use a trigger, but can run into consistency issues that way. Certain bulk-load operations will bypass triggers, ...
2
Most database systems normally handle this with a Check Constraint, but your version of MySQL doesn't have that implemented. So you can implement it with a Trigger as discussed in this StackOverflow answer.
Top 50 recent answers are included
