CODEWITHSUNDEEP
sql
fileames
fileames

Reputation: 13

Can we create table where primary key order does not matter in SQL?

I have a table where I have two foreign keys to the same table and don't want to include same keys with different order.

CREATE TABLE Interacts_With
( 
    student_1 CHAR(20),
    student_2 CHAR(20),

    PRIMARY KEY (student_1, student_2),
    FOREIGN KEY (student_1) REFERENCES Students(id),
    FOREIGN KEY (student_2) REFERENCES Students(id)
)

I don't want to insert both (1,2) and (2,1).

Is there a way to ensure this in the CREATE TABLE statement?

Upvotes: 1

Views: 43

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

One simple method is to ensure that the ids are in order:

CREATE TABLE Interacts_With (
    student_1 CHAR(20),
    student_2 CHAR(20),
    PRIMARY KEY ( student_1  , student_2 ),
    FOREIGN KEY (student_1) REFERENCES Students(id),
    FOREIGN KEY (student_2) REFERENCES Students(id),
    CHECK (student_1 < student_2)
);

Note that you will need to insert the data in the correct order. Otherwise the insert will fail.

Upvotes: 3

Related Questions