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
Related Tags
constraint × 440sql-server × 125
postgresql × 125
foreign-key × 98
database-design × 81
mysql × 71
index × 36
oracle × 33
check-constraints × 28
unique-constraint × 27
primary-key × 23
sql-server-2008 × 19
sql-server-2008-r2 × 18
ddl × 15
sql-server-2014 × 12
alter-table × 12
null × 12
referential-integrity × 12
sql-server-2005 × 10
innodb × 9
trigger × 9
sql-server-2012 × 8
sqlite × 8
delete × 8
default-value × 8