0

I have this table structure and simple relationships:

Simple database

and sample data in the table:

Company Company names are unique and should not be repeated:

+------------+---------------+
| Company_ID | Company_name  |
+------------+---------------+
| 1          | Company_name1 |
+------------+---------------+
| 2          | Company_name2 |
+------------+---------------+

Location (Locations should be assigned to a specific company):

+-------------+------------+-------------------------+
| Location_ID | Company_ID | Location_name           |
+-------------+------------+-------------------------+
| 1           | 1          | Company1_Location_name1 |
+-------------+------------+-------------------------+
| 2           | 1          | Company1_Location_name2 |
+-------------+------------+-------------------------+
| 3           | 2          | Company2_Location_name1 |
+-------------+------------+-------------------------+
| 4           | 2          | Company2_Location_name2 |
+-------------+------------+-------------------------+

Data The data in the table should depend on the selected company, and the locations should be only those that occur in the company:

+---------+-------------+------------+------------+------+
| Data_ID | Location_ID | Company_ID | data_value | date |
+---------+-------------+------------+------------+------+
| 1       | 1           | 1          | 5          | date |
+---------+-------------+------------+------------+------+
| 2       | 2           | 1          | 2          | date |
+---------+-------------+------------+------------+------+
| 3       | 3           | 2          | 3          | date |
+---------+-------------+------------+------------+------+
| 4       | 2           | 1          | 1          | date |
+---------+-------------+------------+------------+------+
| 5       | 4           | 2          | 6          | date |
+---------+-------------+------------+------------+------+
| 6       | 4           | 2          | 7          | date |
+---------+-------------+------------+------------+------+

The main dependencies that should be met:

  • Company names should be unique and attempts to add the same company should be blocked
  • Location names should be assigned to a specific company, but they may repeat and a location may appear in several companies but have a different Location_ID
  • Adding values to the date table should depend on:
    1. company (we choose a specific company for which we add values)
    2. locations (locations must depend on company)

For example:
When adding values for a company with Company_ID = 1, I should only be able to add Location_ID that occur under that company.
If I want to add a value in the data table for Company_name1 then the only available values for the Location_ID column in the data table, should be: Company1_Location_name1 and Company1_Location_name2 and I can't have values there from another company (i.e. Company2_Location_name1 and Company2_Location_name2)

At the moment it works badly:
when adding values to the data table I can select a company, but then I have locations available and I can add values that do not make sense - for Company_name1 I can add a location from Company_name2 but it should be blocked.

How can I solve such a problem? Add some additional table which will be responsible for particular pairing?

DeepSea
  • 305
  • 2
  • 15
  • Is it possible to have data with no location? If not, then location implies company and you should remove Company_ID from the data table. Also, much of this sounds like a user interface problem, rather than anything to do directly with mysql. – Joel Coehoorn Jul 28 '21 at 19:30
  • You can't specify this relationship as an automatic constraint, because subqueries aren't allowed in `CHECK` constraints. You could do it with triggers. – Barmar Jul 28 '21 at 19:32
  • @JoelCoehoorn Everything ok, but the point is that the person who will be adding values to the table `data` to have it as easy as possible, and in this case has only the names of locations that may repeat and then I do not know to which company will be added this data – DeepSea Jul 28 '21 at 19:55
  • @Barmar any example how can i do this under mariadb? – DeepSea Jul 28 '21 at 19:55

1 Answers1

0

Depends what database you use.

A simpler way would be to just create a unique constraint on the table field, this will also enforce it for updates too and remove the need for a trigger. Just do:

Example for MSSQL:

ALTER TABLE [dbo].[Company]    
ADD CONSTRAINT [Company_name] UNIQUE NONCLUSTERED
(
    [CompanyID], [Company_name]
)

and then you'll be in business. You will be not able to add 2 company with the same name. You can find another examples here : Trigger to prevent Insertion for duplicate data of two columns

This is exacly what you are looking for :)

@EDIT 1

OK so if you want example for MARIADB here we go :

Create unique Contraint - Using a CREATE TABLE statement

The syntax for creating a unique constraint using a CREATE TABLE statement in MariaDB is:

CREATE TABLE table_name
(
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...

  CONSTRAINT constraint_name UNIQUE (uc_col1, uc_col2, ... uc_col_n)
);

table_name

The name of the table that you wish to create.

column1, column2

The columns that you wish to create in the table.

constraint_name

The name of the unique constraint.

uc_col1, uc_col2, ... uc_col_n

The columns that make up the unique constraint.

In your example :

CREATE TABLE Company
( Company_ID INT(11) PRIMARY KEY AUTO_INCREMENT,
  Company_name VARCHAR(250) NOT NULL,
  CONSTRAINT company_name_unique UNIQUE (Company_name)
);

In this example, we've created a unique constraint on the Company table called company_name_unique. It consists of only one field - the Company_name field.

Adamszsz
  • 561
  • 3
  • 13