Composite keys

[JackLilhammers]

In my project I have tools and protocols. A tool can communicate with different protocols and a protocol can be used by different tools. Tools and protocols have their tables. I tried to manually create a relationship [n: m] by adding a third tool_protocol table but without composite keys the only way that comes to mind to do this would be to have a third column with an ID that should be some kind of hash of the other 2 columns. I looked into the primary key implementation, but I think that it would be better to have a COMPOSITE_KEY macro. What do you think?

[HamedMasafi]

Hi As I understand, your mean is a mechanism for n:m relation? But in table normalization n:m relation must be splitted into two 1:n and 1:m relation with medium table

[JackLilhammers]

The classic example is students and classes, each students can attend many classes and each class has many students. To map this relation I'd use a student_class table with student_id and class_id as columns, which have to form the primary key. Something like this:

CREATE TABLE student_class (
    student_id integer NOT NULL,
    class_id integer NOT NULL,

    PRIMARY KEY (student_id, class_id),

    CONSTRAINT fk_student
        FOREIGN KEY(student_id) REFERENCES student(id),
    CONSTRAINT fk_class
        FOREIGN KEY(class_id) REFERENCES class(id)
);

Isn't it what you mean by medium table? In this case how do you ensure that each row is unique if not by declaring both the columns a composite key?

[HamedMasafi]

Yes, is that my mean. I think unique constraint for keys must be applies from database and is not related (for now) to orm