LibreOffice Base Primary Key of several columns?

Question

I have created a database with one table like this:

How to create a primary key like: (Patient, Date, Test, Parameter) ?

The database will be holding data for numerous patients examined multiple times on different dates and for each examination multiple tests will be done resulting in multiple parameters and its values plus additional data.

The combination of (Patient, Date, Test, Parameter) will be unique however.

Answer 1

All columns that will be used for the primary key are required to be NOT NULL. To set this, edit the table and for each column, set Entry Required to Yes in the lower area of the editor.

Then go to Tools -> SQL and run the following command.

ALTER TABLE "BloodTestResults" ADD PRIMARY KEY ("Patient", "Date", "Test", "Parameter");

Save, close and reopen the .odb file. Now the primary key should be in effect.

Attempting to add duplicates will produce an error.

This was tested with a Firebird Embedded database setup.

Reference: SQL - Primary Key

Answer 2

CREATE TABLE customer

(

Id integer (15) NOT NULL PRIMARY KEY,

Customer Name varchar (255),

Country varchar (255),

Year varchar (255),

); INSERT INTO Customer ( Id, customer name, country, year ) values ( 1, 'Ronny', 'India', 2020 ); If more information and with more example find Primary key sql

Answer 3

I'm guessing you want those fields to be a primary key because they're unique and other columns aren't. If that is the case you should split those four fields into a separate table, create a single-column primary key for that table, and use it as a foreign key into a separate table containing the non-unique data. Something like:

CREATE TABLE Patient ( id INTEGER PRIMARY KEY, name VARCHAR(255), ... )
CREATE TABLE PatientTest (id INTEGER PRIMARY KEY, patientId INTEGER FOREIGN KEY,
    testDate DATE NOT NULL, test SOMETHING NOT NULL, parameter SOMETHING NOT NULL)
CREATE TABLE TestResult (id INTEGER PRIMARY KEY, patientTestId INTEGER FOREIGN KEY,
    resultImported VARCHAR(255), ... )

Where SOMETHINGs depend on things you haven't told us. This allows multiple tests for each patient, and multiple results for each test.