[SOLVED] SQL DDL address must contain

Issue

How do i write a contraint for an address so it contains both a street address and a number? No need to handle addresses that contain apartment number or floor level. Thanks.

Solution

Where can use a CHECK constraint with RLIKE and a regular expression.
Here we have simply said that there must be a number followed by one or more words with hyphens and underscores allowed.
NB mySQL only enforces this in more recent version. Check this trial script in your version to see whether it works. If it is not enforced the only solution is a trigger ON INSERT

create table user(
  name varchar(25),
  adresse varchar(25) check (adresse rlike '[0-9]+ [- a-zA-Z]+')
  );

insert into user values ('Boris', '10 Downing Street');

insert into user values ('Poutin','The Kremlin');

Check constraint 'user_chk_1' is violated.

db<>fiddle here

Answered By – Kendle

Answer Checked By – Cary Denson (BugsFixing Admin)