CODEWITHSUNDEEP
sqlpostgresql
Phlex
Phlex

Reputation: 441

Postgres - How to reference a primary key to a non unique value

so I've been working on this assignment I got lately and it has me stumped for how I have to reference a specific table. So let's say I have a table of works from the museum and it contains two primary keys, combined makes it alphanumeric.

CREATE TABLE Works (
wrk_charid pdkeyone,
wrk_numid pdkeytwo,
wrk_name workname,
wrk_type worktype,
wrk_subtype worktype,
wrk_donate donator,
wrk_creation workDate,
wrk_acquistion workdate,
wrk_insurance insurance,
wrk_desc description,
wrk_curloc locationname DEFAULT 'Storage'
REFERENCES LocationAreas 
    ON UPDATE CASCADE ,
PRIMARY KEY (wrk_charid, wrk_numid),
UNIQUE (wrk_charid, wrk_numid)

);

So that is my table for works, and we have a separate table for materials. However, many works have more than one value for their materials, causing an error that it is not unique. So far I have the table for my materials as follows:

CREATE TABLE Materials (
mt_charid pdkeyone,
mt_numid pdkeytwo,
mt_material materialdesc,
PRIMARY KEY (mt_charid, mt_numid)

);

I don't know exactly how I can reference my works to my materials without running into a uniqueness error. Could someone please help push me in the right direction for what I'm supposed to do?

Upvotes: 2

Views: 3947

Answers (1)

Troveldom
Troveldom

Reputation: 376

As the relationship is many to many (one work can have many materials, one material can be present in many works) You should create a new Table to add the relationships between them. (Basically which work has which material). Something like this:

CREATE TABLE Works_Materials (
    wrk_charid work_pdkeyone,
    wrk_numid work_pdkeytwo,
    mt_charid material_pdkeyone,
    mt_numid material_pdkeytwo,
    PRIMARY KEY (work_pdkeyone, work_pdkeytwo, material_pdkeyone, material_pdkeytwo)
    FOREIGN KEY (work_pdkeyone, work_pdkeytwo) REFERENCES Work(pdkeyone, pdkeytwo)
    FOREIGN KEY (material_pdkeyone, material_pdkeytwo) REFERENCES Material(pdkeyone, pdkeytwo)
);

Upvotes: 3

Related Questions