2

I want to make a relationship between two tables.

I have a table A (project), with project_id as a primary key.
And a table B (organization). Projects can have many organizations.

The problem is that table B has values in project_id that are not in table A (but will be in upcoming tables).

Is there a way to allow the relationship even if there are non-existent keys? Or do I have to drop rows with violating values from table B?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Ignacio Basti
  • 97
  • 2
  • 10
  • I don't understand what you are trying to achieve. There is a projects table. And a project can have many organizations, but an organization can only belong to one project. An m:n relation. Yes? Then have a project ID in the organizations table. Are there organizations that don't belong to a project? Then make the project ID nullable. But what could it possible mean to have a project ID in the organizations table that does not exist in the project table? This doesn't seem to make sense. Please elaborate. – Thorsten Kettner May 29 '21 at 22:27
  • 1
    Seems that you are looking for some SQL trickery in order to deliberately introduce a *logical error* in the DB. Not a good idea. Step back and think it through. – Damir Sudarevic May 30 '21 at 14:32

1 Answers1

1

The whole purpose of a FOREIGN KEY constraint is to disallow rows in table B that would violate the constraint - and be sure of it.

That said, you can make use of the default MATCH SIMPLE behavior of multicolumn FK constraints for an elegant solution: FK is not enforced if at least one included column is NULL. See:

You need an additional column, the boolean flag valid in my demo:

CREATE TABLE project (
  project_id int NOT NULL GENERATED ALWAYS AS IDENTITY
, project text NOT NULL
, project_id_valid boolean NOT NULL DEFAULT true  -- NOT NULL!
, PRIMARY KEY(project_id, project_id_valid)  -- !
, CONSTRAINT project_id_always_valid CHECK (project_id_valid)  -- only true
);
COMMENT ON COLUMN project.project_id_valid IS 'Redundant column to allow conditional multicolumn FK reference in table organization';

INSERT INTO project(project) VALUES ('project1');

CREATE TABLE organization (
  org_id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, org text NOT NULL
, project_id integer NOT NULL
, project_id_valid boolean DEFAULT true  -- can be NULL!
, CONSTRAINT valid_project_fk FOREIGN KEY (project_id, project_id_valid) REFERENCES project -- !
, CONSTRAINT project_id_valid_or_null CHECK (project_id_valid)  -- only true
);
COMMENT ON COLUMN project.project_id_valid IS 'TRUE enforces the FK constraint valid_project_fk.';

db<>fiddle here

The model is simplified. Actual organizations would have multiple projects and you would use a many-to-many implementation. See:

CONSTRAINT project_id_always_valid CHECK (valid) in combination with NOT NULL on the column enforces project.project_id_valid to always be true, which makes it a noise column. We need it for the multicolumn PK to allow our multicolumn FK constraint in organization. (Alternatively, use a plain PK on (project_id) and add a multicolumn UNIQUE constraint on (project_id, project_id_valid).)

The corresponding column organization.valid can be true or NULL. If it's true, the FK constraint is enforced, else it's not. Effectively, you can switch the FK on or off per row. Exactly what you are after.

These are standard SQL features. Still, be sure to document the purpose of the trickery for the afterworld. I added comments on the column project_id_valid in this spirit.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Erwin, please don't provide answers to such vague and under-specified questions. Your answer is almost completely guesswork. The OP must tell us their full schema, including what are declared as Keys and Foreign Keys. Having a `project-Id` in table `B` that is sometimes a Foreign Key, and sometimes a dangling pointer smells to me like terrible schema design. Introducing a nullable column (especially a key) smells worse. As per @Thorsten's comment, the OP should explain what is the business requirement here. – AntC Jun 01 '21 at 05:51
  • 1
    @AntC Sure, [an example](https://stackoverflow.com/help/minimal-reproducible-example) always helps to clarify. But this question is clear to me as it is. You seem to dislike nullable FK columns. OK, tell the world. I disagree. Postgres disagrees. The SQL standard disagrees. The default `MATCH SIMPLE` behavior of FK constraints is implemented proof of this. I even lead with the basics and a link to documentation. There is nothing misleading here. Nothing to justify a comment telling me not to answer. – Erwin Brandstetter Jun 01 '21 at 23:46