Request for marking one or more database columns as unique

Feature Requests
1

Having asked the question in “Anvil Q&A” already, I got some useful answers but sadly none REALLY satisfied. If I try to achieve the goal in Python one could still enter erroneous rows in the Anvil editor…

BTW: Yes, I know that I could try and work around SOME of the problems by defining cell types as “Multiple rows of another table”, but this would not work for other column types.

I searched for a way to mark one or more rows of a database table to be UNIQUE. In PostgreSQL it is possible to define database tablles as follows:

CREATE TABLE IF NOT EXISTS employee 
(emp_id	INT,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50),
gender CHAR(1),
birthdate	DATE,
email	VARCHAR(100),
salary		INT,
UNIQUE (first_name, last_name));

Could it be possible to enhance the “Tables” to mark “first_name” and “last_name” as unique as this is possible in SQL already. If a user tries to enter data via the Anvil table editor or by adding rows in Python this should result in an error message being shown or a thrown exception respectively.

If more than one column is checked, all entered combinations for the marked columns must to be unique.

I think this would be a more or less minor change and would have a huge benefit. If the added checkbox would stay unchecked as default, this would not effect existing code or tables.

One column unique:

grafik
grafik1676×1069 112 KB

Both columns combined must be unique:

grafik
grafik973×765 91.9 KB

Thanks for considering,
Hampf

2

How would you distinguish between

  1. Both columns combined must be unique (e.g., a composite primary key)
  2. Each column individually must be unique (e.g., internal and external ids)

?

3

I would leave the decision to make one or both possible or which one to use to the designers’ choice.

I personally feel that composite primary keys would be preferrable - and concidentally that is exactly what I need. :upside_down_face:

I would choose whichever is actually the most practicable using plain SQL.

Cheers,
Hampf

4

If it’s up to the designer, not to Anvil, then they’ll need to provide Anvil with more information, so that it can disambiguate the two interpretations. They’re functionally very different, and in SQL, equally practical.

5

That is completely correct but also only a “problem” of proper documentation. If the Docs state one or the other (or both and how to distinguish them) it still would be a huge benefit.

If #1 (combined primary key) is a quick win, the go for it and just document, that all checked rows must be unique in combination. If #2 (every marked column must contain unique values) would win the race than so be it. Having neither of the coices at the moment is by far worse than just missing the desired “other” option.

If you try and go for the “eierlegende Wollmilchsau” and provide a mixture of both and even combinations of them allowing more than one combined primary key AND unique individual columns I agree that this would be a major task.

Maybe the problem could be solved by providing more than one line of definitions. with dropdown selections of existing table columns.

[… table row/columns definition above as usual …]
[Unique] [first_name] [last_name] [+]
[Unique] [internal id] [+]
[Unique] [external id] [+]
[Add constraint]

Cheers,
Hampf

6

I’m not suggesting that it’s a major task. Both are quite easy in SQL. I’m simply pointing out that two checkboxes is not enough information for the designer to clearly distinguish, to Anvil, between the two possibilities.

SQL’s approach is probably the cleanest. SQL can make any column unique in itself (the “unique” modifier on a column is equivalent to a checkbox on that column).

But to specify a combination of columns to be unique (as in a multi-part key), the combination is declared as being unique. Explicitly. Likewise with multi-part foreign keys. Just as you suggest, anything multi-column could be under an [Add constraint] button.

Constraints of all kinds are an important part of making storage reliable, in the sense of rejecting obvious attempts at putting garbage into storage (or mutating what’s already there into garbage). “Garbage” in the sense of violating the business data rules your App depends on.

Even a small step in automating those checks is generally good thing. One can’t implement every possible check, but the most common checks should probably take a priority. Unique columns would be quite a good start.

Another approach to doing sanity checks uses callbacks: the database calls one of your functions, to figure out whether something should be allowed. This is extremely general, but would probably be quite slow.