UNIQUE constraints on table columns possible?

Unique table columns possible?

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));

I haven’t found something equal when defining Anvil database tables.

Manual testing for uniqueness is uneccessary, tedious and error prone. But maybe I just do not see what already is there right before my eyes.

What I’d like to do is define a “Users” table with unique user names, a table “Teams” with unique team names plus a table TeamUsers with unique “user_name” and “team_name” to keep track of users and their assigned teams.

And yes: I am aware that it would be possible to link from Users to Teams using “Multiple rows from another table”. This would not remedy the problem, that a table definition with unique “first_name” (and possibly additional “last_name”) is not possible without unique constraints.

And yes: I am aware that there WOULD arise problems if there is more than one John D. Owe. This is only an example. :wink:

Any thoughts?

Cheers,
Hampf

Yes, it’s possible.

I handle this by funnelling all db i/o (for the table in question) thru its own custom anvil.server.call routines (e.g., for single-row create, read, update, and delete). These do all the necessary database level constraint-enforcing that I want done.

Of course, I have to code those routines (and their constraint-enforcers) myself. But as long as I stick to those routines, integrity is assured.

If the table is shared (and updated) by multiple Apps, then all of them need to use the same (or equivalent) routines. This may be made easier if those routines are placed in a Dependency (library-style App), that all the Apps can share and use.

3 Likes

(I’ve never done it, but) I would also create a nightly task that checks for duplicated and sends me an email, just in case something unexpected goes wrong.

2 Likes

I think some of the higher plans allow the use of sql on the table so would probably allow this… Otherwise I think the application will have to handle it.

Also, the Users table, if you are using the built in Anvil user-signup, automatically only allows each email to only sign up once.

This sounds like it would make a good feature request, if it hasn’t already been made.

2 Likes

The answer in the past was always that this is unnecessary, since the data tables are meant to be used in python, you can just sanitize your input by using the .get() logic with or short circuiting on a table to achieve the same results.
Instead of a unique constraint existing at the database engine level which raises engine errors, it is handled by sanitizing inputs at the python level.

some_possibly_conflicting_data = "12345"
upsert_data_for_column_foo = "678910"

a_new_or_the_same_row = app_tables.some_table.get(unique_col=some_possibly_conflicting_data ) or app_tables.some_table.add_row(unique_col=some_possibly_conflicting_data )

a_new_or_the_same_row['column_foo'] = upsert_data_for_column_foo 
3 Likes

@ianb I like the “get() or add_row()” approach for upserts, which is not really a solution for my problem because I do not want to update or insert. But I hope I’ll remember this “best practice” when I need it.

@p.colbert I agree that creating a global dependency for checking multi-column-uniqueness is a feasible way to resolve the problem, but I think it would be easier to handle an exception after having checked some boxes in the already defined database tables’ header like shown in the (edited :grin:) screenshot. This would also have the nice effect that entries can be checked for uniqueness even when entering values in the Anvil editor.

or like this for multi-row-unique data:

So I’ll try and shove this topic over to “Feature Request”

Cheers,
Hampf

3 Likes

I like to classify db constraints by what information they need (and how much of it):

  1. The value in a single cell (row and column) (check constraint)
  2. Selected values from a single row (check constraints)
  3. Selected values from all rows (unique, primary key)
  4. Selected values from another table (e.g., foreign keys, cardinality)

If implemented as callbacks, these would have different function signatures.

I don’t even have an opinion if what I was suggesting should be done, just that this was what was explained in the past as the reason why database level constraints on uniqueness are unnecessary.

You will have to put data in a data table at some point, or you are not using the database? The answer to “why is there no database level constraints” is “you should sanitize your inputs” by doing something akin to what @p.colbert describes, so:

I am not an advocate for doing it this way, in fact I use SQL on the regular and rely on everything you want as a feature as well, its just that the data tables service is a way for people who only know python to use it, so I was just trying to shed light on why it was built this way.

There are many SQL features that don’t exist that could be FR’s, real JOINs, etc. a unique constraint column is definitely one of them.

@ianb Ah, JOINs, I forgot. Definitely a FR.

As said I like your upsert example.

Let’s wait and see what SQL has to offer to provide a few quick wins for Anvil. As I am only dealing with only a few tables with few entries I am perfectly fine using the python approach at the moment.

Cheers,
Hampf