SQL DDL address must contain-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

You can use regex in a CHECK constraint:

check(street_address RLIKE'^[0-9]*[[:space:]].*$')

Or whatever makes sense for your column