How And Why To Add Primary Keys To My Sql Database Table When I Already Have An Index

I have the following sql table called datatable, date yesteday today tomorrow reading source 2021-01-01 x x x x 1 2021-01-01 x x x x

Solution 1:

A primary key is a constraint that specifies that the values in its column(s) must be (mutually) unique and not null. This is so that it can be guaranteed to uniquely identify every row in the table, which is most useful when wanting to create foreign keys that refer to this table. It's still worth having if you don't have other tables, as it will prevent your table getting into a bad state where, for example, you have more than one of the same source value on a particular date.

The primary key field(s) will almost always have an index on them as well as they are used often for lookups and JOINs, but the two concepts are separate.

Some DBMSs (e.g. MySQL, SQL Server) automatically create a clustered index on the primary key, meaning that data in the table is sorted on disk by the field(s) comprising the primary key to make the common operations above even faster. However, postgres does not do this by default.

You can specify the primary key on table creation using the following syntax:

createtable datatable 
(
    datedate, 
    yesterday real, 
    today real, 
    tomorrow real, 
    reading real, 
    source varchar,
    PRIMARY KEY (source, date)
)

You can also add a new, auto-incrementing integer field to act as your primary key (generally referred to as a surrogate key or artificial key). You might want to do this if you don't have any other good candidates in your data items, but it can have other benefits as well (potentially faster JOINs for example).

createtable datatable 
(
    id serial primary key,
    datedate, 
    yesterday real, 
    today real, 
    tomorrow real, 
    reading real, 
    source varchar
)