Creating PostgreSQL tables + relationships - PROBLEMS with relationships - ONE TO ONE

Question

So I am supposed to create this schema + relationships exactly the way this ERD depicts it. Here I only show the tables that I am having problems with:

So I am trying to make it one to one but for some reason, no matter what I change, I get one to many on whatever table has the foreign key.

This is my sql for these two tables.

        CREATE TABLE lab4.factory(

            factory_id      INTEGER         UNIQUE,
            address         VARCHAR(100)    NOT NULL,
            PRIMARY KEY ( factory_id )

        );

        CREATE TABLE lab4.employee(

            employee_id     INTEGER         UNIQUE,
            employee_name   VARCHAR(100)    NOT NULL,
            factory_id      INTEGER         REFERENCES      lab4.factory(factory_id),
            PRIMARY KEY ( employee_id )

        );

Here I get the same thing. I am not getting the one to one relationship but one to many. Invoiceline is a weak entity.

And here is my code for the second image.

        CREATE TABLE lab4.product(

            product_id      INTEGER     PRIMARY KEY,
            product_name    INTEGER     NOT NULL

        );


        CREATE TABLE lab4.invoiceLine(

            line_number     INTEGER     NOT NULL,
            quantity        INTEGER     NOT NULL,
            curr_price      INTEGER     NOT NULL,
            inv_no          INTEGER     REFERENCES      invoice,
            product_id      INTEGER     REFERENCES      lab4.product(product_id),
            PRIMARY KEY ( inv_no, line_number )

        );

I would appreciate any help. Thanks.

Answer 1

One-to-one isn't well represented as a first-class relationship type in standard SQL. Much like many-to-many, which is achieved using a connector table and two one-to-many relationships, there's no true "one to one" in SQL.

There are a couple of options:

• Create an ordinary foreign key constraint ("one to many" style) and then add a UNIQUE constraint on the referring FK column. This means that no more than one of the referred-to values may appear in the referring column, making it one-to-one optional. This is a fairly simple and quite forgiving approach that works well.

• Use a normal FK relationship that could model 1:m, and let your app ensure it's only ever 1:1 in practice. I do not recommend this, there's only a small write performance downside to adding the FK unique index and it helps ensure data validity, find app bugs, and avoid confusing someone else who needs to modify the schema later.

• Create reciprocal foreign keys - possible only if your database supports deferrable foreign key constraints. This is a bit more complex to code, but allows you to implement one-to-one mandatory relationships. Each entity has a foreign key reference to the others' PK in a unique column. One or both of the constraints must be DEFERRABLE and either INITIALLY DEFERRED or used with a SET CONSTRAINTS call, since you must defer one of the constraint checks to set up the circular dependency. This is a fairly advanced technique that is not necessary for the vast majority of applications.

• Use pre-commit triggers if your database supports them, so you can verify that when entity A is inserted exactly one entity B is also inserted and vice versa, with corresponding checks for updates and deletes. This can be slow and is usually unnecessary, plus many database systems don't support pre-commit triggers.