How to add unique constraint to columns across tables in SQLite

Question

Say I have two columns: GroupName in one table, and Username in another. How do I constrain the values of these columns so that any value added to one column is unique across both columns? Preferably in a "non-messy" way.

share the input sample first and what you want to get as desired output sample — Himanshu, Dec 15 '18 at 09:43
Check this https://stackoverflow.com/questions/10337944/sql-unique-constraint-across-multiple-tables — Kedar Limaye, Dec 15 '18 at 09:44
@KedarLimaye not what I was hoping for, but it is what I was looking for. Thanks — Garikai, Dec 15 '18 at 09:59

score 0 · Answer 1 · answered Dec 15 '18 at 12:03

There is no simple way with the structure that you mention. Of course, there is a way by implementing triggers, but that is cumbersome.

An alternative data model fixes the problem, though:

create table things (
    thingsId int auto_increment primary key,
    name text,
    type text,
    . . .
    check (type in ('group', 'user')),
    unique (name)
);

This puts each type of thing into a single column and then imposes the unique constraint.

How to add unique constraint to columns across tables in SQLite

1 Answers1