MariaDB创建人名格式校验CHECK约束求助（替代INITCAP）

Got it, let's fix this problem! Since INITCAP() isn't allowed in MariaDB CHECK constraints, regular expressions are the perfect workaround here. We can build a regex pattern that enforces your rule: every word (after a space) must start with an uppercase letter, followed by lowercase letters.

Step-by-Step Implementation #

1. Craft the Regex Pattern
   The pattern ^[A-Z][a-z]*(\s[A-Z][a-z]*)*$ does exactly what you need:

   • Ensures the entire name starts with an uppercase letter
   • Requires each subsequent word (after a single space) to also start with an uppercase letter
   • Restricts all other characters in each word to lowercase letters
   • Blocks extra spaces or non-alphabetic characters (tweak this if you need to allow hyphens or apostrophes later)

2. Create the Table with the CHECK Constraint
   Here's a complete example of a table using this validation rule:

   CREATE TABLE user_profiles (
       profile_id INT AUTO_INCREMENT PRIMARY KEY,
       full_name VARCHAR(150) NOT NULL,
       CONSTRAINT chk_valid_full_name CHECK (full_name REGEXP '^[A-Z][a-z]*(\s[A-Z][a-z]*)*$')
   );
   

Test the Constraint Against Your Examples #

Let’s verify with the cases you provided:

• ✅ Pepe Antonio → Passes (follows the required format perfectly)
• ❌ pepe Antonio → Fails (first word starts with a lowercase letter)
• ❌ Pepe antonio → Fails (second word starts with a lowercase letter)
• ❌ PEPE ANTONIO → Fails (all letters are uppercase, not just the first of each word)
• ❌ pepe → Fails (single word starts with lowercase)

Quick Adjustments for Edge Cases #

• If you need to allow hyphenated names (like Mary-Ann Smith), update the regex to ^[A-Z][a-z-]*(\s[A-Z][a-z-]*)*$
• The pattern already blocks multiple consecutive spaces (it only matches single spaces between words)
• Make sure you’re running MariaDB 10.2.1 or newer—regex support in CHECK constraints was added in this version

内容的提问来源于stack exchange，提问作者user12866845