CODEWITHSUNDEEP
sqldatabasepostgresqldatabase-design
robotcourier
robotcourier

Reputation: 27

SQL: Composite primary key as foreign key

I'm creating a database in postgres about NY counties on their education aid amount and criminality rates ranging from 2010 to 2019, these are the two tables

CREATE TABLE NYAidCrimeTimeSeries 
(
    County VARCHAR(50),
    Year int,
    AidAmount int,
    Population int,
    JailPopulation int,
    CrimesReported int,
    PRIMARY KEY (County, year)
)

CREATE TABLE NYAidCrimeMean 
(
    County VARCHAR(50),
    AidAmount_mean int,
    Population_mean int,
    JailPopulation_mean int,
    CrimesReported_mean int,
    AidPerCap int,
    CrimesPerCap int,

    FOREIGN KEY (County) REFERENCES nyaidcrimetimeseries (County)
)

Would this be possible? Having a composite primary key as a foreign key? If not, what direction should I go try?

Upvotes: 0

Views: 1442

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

The purpose of a foreign key is to guarantee that one or more keys in one table have a corresponding row in another table. The "a" in this context is singular. And, in general, foreign keys should be using primary keys. Any unique key is allowed, but primary keys are strongly recommended.

Your data model is just begging for a counties table. At the very least:

create table counties (
     county_id int generated always as identity primary key,
     name varchar(255)
);

The county_id can then be a foreign key in both the other tables. Well, in fact, perhaps the summary statistics could also be columns in counties.

Hmmmm . . . It is possible that you just have the foreign key definitions in the wrong place. You want county to be the primary key of NYAidCrimeMean and then for NYAidCrimeTimeSeries to reference that table.

If you do take this approach, I would suggest renaming NYAidCrimeMean because the table name does not suggest that it is one row per county. At least to those not familiar with your domain.

Upvotes: 3

Related Questions