Circular dependency in schema or boolean with CHECK? Let's say I have the following tables:

create table repositories (
    id serial primary key,
    name varchar(255) not null
);

create table remotes (
    id serial primary key,
    url varchar(255) unique not null,
    repository_id integer references repositories(id) not null
);


I want to have an "origin" remote for a repository.

I have 2 solutions:


Create a circular dependency, which means using ALTER TABLE in the schema:

alter table repositories add origin_remote_id integer references remotes(id);

Add a flag on remotes with a custom CHECK constraint:

create table remotes (
    id serial primary key,
    url varchar(255) unique not null,
    repository_id integer references repositories(id) not null,
    is_origin boolean not null constraint is_only_origin check (is_only_origin(repository_id))
);



The problem is that the 1st approach makes relationally more sense, but it also introduces a circular dependency.

Is it fine? I'm not sure what I should do.
Having the origin_remote column in the repositories table allows for an intrinsic consistency, even without check, and you'll keep this consistency when changing DBMS and, more importantly, in the application code.

This also allows for a single query when you want to change the origin of a repository, while having your is_origin boolean would imply two queries which would have to be wrapped in a transaction.

In this specific case I'm of the opinion you should really use the standard relational link from repositories to origin_remote_id. The need to use alter table is just as a minor inconvenience.