There are 3 entities:

  1. User - describes common attributes
  2. Category - individual
  3. Category - legal entity

How to organize data integrity restriction correctly so that User can only be in one category?

The following comes to mind:

  1. Hang triggers on categories, so that when adding a user to a category, the opposite category is checked for the presence of the user in 1 of the categories

  2. Create a procedure in which, within a single transaction, records will be created in: User + Legal entity or User + Individual

What is most true and are there any other ways?

  • IMHO, not categories should refer to the user, but rather the user - to categories. Accordingly, Users will have 2 foreign keys on the category tables, and a corresponding constraint, something like this: CHECK (CASE WHEN fk1 IS NULL THEN 0 ELSE 1 END + CASE WHEN fk2 IS NULL TNEN 0 ELSE 1 END = 1) (this if the user must fall into one of two categories). In general, the most correct way is not to modify the records in the tables manually, and provide the necessary logic with stored procedures. - Yaant

2 answers 2

You can do without triggers, i.e. Make a declarative integrity constraint - CHECK + UDF. A similar issue is addressed here .

  • Looks like what you need. Thank! - iluxa1810 4:05 pm
  • What will work faster: CHECK, which will call a function, which in turn will look in the opposite category, or a trigger that will do similar actions? - iluxa1810
  • Conduct experiments. Or do you prefer to take the word? :-) - msi

In my experience in the banking sector, the most correct decision is the separation of legal entities and individuals according to different tables (in the 3 banks in which I worked - TOP 10 in Russia, this is done). This decision is explained very simply - the attributes of businesses and individuals are very different.