4

This question already has an answer here:

I have a table:

CREATE TABLE methods
(
    method_id serial PRIMARY KEY,
    method_name varchar(100)
);

I now want to create a table with the following columns:

CREATE TABLE experiments 
(
    method integer[] REFERENCES methods(method_id),
    trials integer
);

I get an error:

Key columns "method" and "method_id" are of incompatible types: integer[] and integer.

I understand that the columns have to be the same type and I also saw that some tried to tackle this foreign key on array issue already:

PostgreSQL 9.3 development: Array ELEMENT Foreign Keys

My interest is that 'method' should be an array of integers referencing 'method_id' from table 'methods' but I can't figure it out. I thought that the link above might be a solution, but seems that was not implemented (?)

Some posts propose using junction/join tables:

Foreign key constraint on array member?

I am an absolute beginner and I could not figure it out yet. For me, it is a matter of understanding first how to tackle this issue of a foreign key on an array. Multiple methods can be considered as forming a class of methods. Each class can have a certain number of trials.

marked as duplicate by ypercubeᵀᴹ, Marian, Max Vernon, Andriy M, Tom V - Team Monica May 10 '16 at 10:50

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

6

As you simply cannot create a foreign key against an array column, you should store the experiments in the proper relational way. This is what you mention as a junction table.

It is a table like the following:

CREATE TABLE experiments (
    method_id integer REFERENCES methods (method_id),
    method_class_id integer REFERENCES method_class (method_class_id)
);

Here you will have one row per method-class pair (which, in turn, may mean several trials, based on your comments above), instead of just one per trial as in your current design. This means a slight storage overhead, but it gives you the flexibility - queries will use proper joins which can be supported by indexes, for example. Also, removing methods from a class will be much easier.

Note: I suppose above that you have a method_class table, too.

  • Hi @dezso! Thanks for your reply. I am afraid that I don't understand what "trials" table is in this context and how should look like. – Valentin Jan 21 '16 at 18:01
  • 1
    @ValSt well, I got your table definition above to guess what you want. So, it's you who should know what trials is about. If you manage to describe this in English (as opposed to any programming (pseudo)language), as DeadEye asks for, it could help a lot understanding your requirements. At the same time, I'd suggest getting a good book or tutorial about relational databases and data modeling. – dezso Jan 21 '16 at 20:18

Not the answer you're looking for? Browse other questions tagged or ask your own question.