How to check for not null value in relationship table in PostgreSQL?-CodePudding

I have these three tables in a PostgreSQL db and as you can see books has an author field (referencing users) and users has a company_id field referencing companies.

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


create table users (
    id serial primary key,
    email varchar not null,
    company_id int,
    constraint fk_company
        foreign key(company_id)
            references companies(id)
);


create table books (
    id serial primary key,
    title varchar not null,
    author_id integer not null,
    constraint fk_author
        foreign key(author_id)
            references users(id)
);

A user may or may not belong to a company, but to create a book the user must have a company reference.

I am wondering if there is a way to implement a CHECK constraint on the author_id column which would ensure that the author has a company reference.

Maybe something like: author_id integer not null CHECK(users.company_id is not null), but of course this doesn't work.

Is there some sort of constraint I can use to check columns in relations?

Also am I even approaching this problem in the right way?

Thanks in advance. :)

CodePudding user response：

Looking at constraints:

A Publisher can have zero or more publications.

An Author/User can have zero or more books Authored.

A Book will have one Publisher & one or more Authors.

Move the company_id constraint to books table. If & when an User/Author[s] publishes a book; They become a new Publishing Company and an entry to Publishing Company.