Understanding the Error: Database Prototype Postgresql

The Problem with Foreign Keys and Primary Keys

As a developer working on a database prototype, it’s essential to understand the relationships between tables and how foreign keys work. In this article, we’ll explore the error message “there is no unique constraint matching given keys for referenced table ‘messages’” and provide guidance on how to fix it.

The Database Schema

The provided SQL create table statements show a complex database schema with multiple weak entities: Patients, Messages, and Attachments. Each entity has foreign key references to the primary keys of other tables. Let’s break down each table:

Patients Table

CREATE TABLE IF NOT EXISTS Patients(
    CPR_number integer NOT NULL,
    first_name varchar(20),
    last_name varchar(20),
    zip_code integer,
    address varchar(100),
    country varchar(20),
    history text,
    PRIMARY KEY (CPR_number)
);

The primary key of the Patients table is CPR_number, which is an integer.

Messages Table

CREATE TABLE IF NOT EXISTS Messages(
    CPR_number integer,
    topic text,
    body text,
    date_send varchar(8),
    date_read varchar(8),
    PRIMARY KEY (body, date_send),
    FOREIGN KEY (CPR_number) REFERENCES Patients(CPR_number)
);

The primary key of the Messages table is a composite index consisting of two columns: body and date_send. The foreign key constraint references the CPR_number column in the Patients table.

Attachments Table

CREATE TABLE IF NOT EXISTS Attachments(
    CPR_number integer,
    file text,
    body text,
    date_send varchar(8),
    PRIMARY KEY (file),
    FOREIGN KEY (body) REFERENCES Messages(body),
    FOREIGN KEY (date_send) REFERENCES Messages(date_send),
    FOREIGN KEY (CPR_number) REFERENCES Patients(CPR_number)
);

The primary key of the Attachments table is file, which is a text column. The foreign key constraints reference the body and date_send columns in the Messages table, as well as the CPR_number column in the Patients table.

Understanding the Error

When you try to create an entry in the Attachments table that doesn’t exist in the Messages table (i.e., the foreign key constraint is not met), Postgresql returns an error message indicating there is no unique constraint matching given keys for referenced table ‘messages’.

This error occurs because the primary key of the Messages table is a composite index consisting of two columns: body and date_send. When you try to create an entry in the Attachments table, Postgresql doesn’t know which combination of values from these two columns uniquely identifies each row in the Messages table.

Correcting the Error

To fix this error, you need to ensure that your foreign key constraints reference primary keys. In this case, you should use synthetic primary keys (e.g., message_id) instead of composite indexes like (body, date_send).

Here’s an updated version of the database schema:

CREATE TABLE IF NOT EXISTS Patients (
    patient_id int generated always as identity primary key,
    CPR_number integer,
    first_name varchar(20),
    last_name varchar(20),
    zip_code integer,
    address varchar(100),
    country varchar(20),
    history text
);

CREATE TABLE IF NOT EXISTS Messages (
    message_id int generated always as identity primary key,
    patient_id int,
    topic text,
    body text,
    date_send date,
    date_read date,
    FOREIGN KEY (patient_id) REFERENCES Patients(patient_id)
);

CREATE TABLE IF NOT EXISTS Attachments (
    attachment_id int generated always as identity primary key,
    message_id int,
    file text,
    FOREIGN KEY (message_id) REFERENCES Messages(message_id)
);

In this updated schema, the Messages table has a synthetic primary key message_id, which is generated using an identity function. The foreign key constraints reference this primary key.

By making these changes, you ensure that your foreign keys correctly reference primary keys, and the error message should disappear.

Additional Best Practices

As mentioned in the answer, there are several best practices to keep in mind when designing your database schema:

• Dates should be stored as date data type instead of strings.
• Foreign keys should reference primary keys.
• Don’t repeat data fields in multiple tables. Values should be stored only once.

Conclusion

Designing a robust database schema requires careful consideration of relationships between tables and how foreign keys work. By understanding the error message and correcting your schema to use synthetic primary keys, you can ensure that your database is stable and efficient. Remember to follow best practices for storing dates and repeating data fields in multiple tables.

Last modified on 2023-12-27