Circular dependency in schema or boolean with CHECK?

Question

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.

Check this: Complex foreign key constraint in SQLAlchemy (the answer by Erwin) and the answers here: How to have a one-to-many relationship with a privileged child? - but keep in mind that they are for SQL Server, which has not deferrable foreign key constraints - so the circular dependency is really unadvisable there. — ypercubeᵀᴹ, May 05 '15 at 14:56
If your question is similar to the questions above (i.e. you a repository has many remotes but only one of them is declared the "origin"), then the repositories -> remotes FK in the 1st solution is wrong, it should include two columns. — ypercubeᵀᴹ, May 05 '15 at 15:00
@ypercube thanks a lot! The other questions are exactly what I was looking for. And indeed, the 1st solution is wrong. — Florian Margaine, May 05 '15 at 15:11
The 2nd is also not complete. You need a partial index to ensure that only one of the remotes has is_origin set to true. — ypercubeᵀᴹ, May 05 '15 at 15:12

score 1 · Answer 1 · answered May 05 '15 at 13:46

1

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.

answered May 05 '15 at 13:46

Denys Séguret

185
7

How do you ensure in this case that the origin is really just an origin? To me it looks like you have to pay this price somewhere. – András Váczi May 05 '15 at 14:45
@dezso As I understand the question, the origin is just the remote that is chosen as origin. But OP should confirm|infirm – Denys Séguret May 05 '15 at 14:53
@dezso I confirm what dystroy said. Sorry if I was unclear. – Florian Margaine May 05 '15 at 14:54
1

The current create table scripts (1st solution) allow for a repository to have a remote origin that is related to a different repository. I don't think this is wanted. – ypercubeᵀᴹ May 05 '15 at 15:01

Circular dependency in schema or boolean with CHECK?

1 Answers1