0

In PostgreSQL, if I store a specific datatype (ex. an mail address) in multiple columns across multiple tables, I could simply define a custom mail type with all constraints like so:

CREATE TYPE status_enum AS enum ('accepted', 'declined', 'awaiting');
CREATE DOMAIN mail_address
    AS varchar(100) CHECK(value ~ '[A-Za-z0-9.]+@[A-Za-z0-9]+[.][A-Za-z]+');

and then use it like so:

CREATE TABLE user (
    ...
    personal_email    mail_address    NOT NULL UNIQUE,
    work_email        mail_address    NOT NULL UNIQUE,
    status            status_enum     NOT NULL,
    ...
);

CREATE TABLE job (
    ...
    status            status_enum     NOT NULL,
    client_email      mail_address    NOT NULL UNIQUE,
    ...
);

I found out that MySQL is not object-relational database and therefore doesn't have custom datatype.

My question is:

How do people handle in MySQL reoccurring datatype in multiple column, in order to prevent copy-pasting identical constraints?

Programer Beginner
  • 1,377
  • 6
  • 21
  • 47
  • 2
    People do not "prevent copy-pasting identical constraints". Or they normalizes the structure and stores all emails in one table. – Akina Apr 19 '21 at 08:55
  • You are not going to find a like-for-like solution, you have to work within the constraints of mysql. If data in multiple tables are instances of the same class, then you can create one table to hold all instances of that class and use foreign keys to reference specific instances. – Shadow Apr 19 '21 at 09:05
  • while it's a good idea to normalize structure and put all user email only in ```users.email```, sometimes we need to store email in multiple table (like ```customer.email```, ```employee.email_personal```, and ```employee.email_corporate```). It simply doesn't make much sense to make a separate ```emails``` table and then make column ```users.email_id```, ```employee.email_id_personal```, ```employee.email_id_corporate``` that references to ```emails``` table – Kristian Apr 19 '21 at 09:06
  • 1
    also, apps that user mysql often (at least that's what I'm doing) validate the data in server-side/application layer rather than catching database error/warnings – Kristian Apr 19 '21 at 09:09
  • @Kristian I think your reaction is too specific for the example provided. The general question is: how can custom data types be simulated within mysql without manually repeating the same constraints again and again. The answer is: store the data in a common table. If that data model does not make sense in a particular case, then unfortunately there is nothing the OP can do in mysql to avoid repeating the constraints and other properties of a field. – Shadow Apr 19 '21 at 09:47

1 Answers1

0

For the first, you can implement using a reference table:

create table statuses (
     status int primary key,
     status_name varchar(255)
);

The relationships would then be handled using foreign key constraints. This is not "arcane" by the way, but how the relationship would be handled in most databases.

The second would be handled by a user-defined function. Most databases support this in a check constraint. Unfortunately, MySQL requires that you use triggers for the data validation -- but they can all use the same underlying function.

Alternatively, create a table of emails and validate the emails only in that table. All other emails would use email_id rather than the email itself. You might find this forward looking. I have found that storing such PII in a separate table is helpful for privacy reasons as well.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    Unfortunately, MySQL requires that you use triggers for the data validation -- but they can all use the same underlying function. Prior to MySQL 8.0.16, yes -> after MySQL 8.0.16 check is finally working see https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html – Raymond Nijland Apr 19 '21 at 11:01
  • can you please provide a simple example for a single trigger validating 3 columns (2 of them in same table, one in different table)? Also, can you elaborate on how storing in separate table in MySQL helpful for privacy? (might be off-topic though)_ – Programer Beginner Apr 19 '21 at 11:11