CODEWITHSUNDEEP
sqlpostgresql
user4194399
user4194399

Reputation:

self relation in sql making tables

I wanted to make a database containing articles, but each article can refer to related articles. So I have an N:M relation from an article to another article. Now I don't know how to implement it. This is what I want:

Related_articles:

Article_number_1(integer), Article_number_2(integer)

Primary key:{article_number_1, article_number_2}

Foreign key: article_number_1(reference: article), article_number_2(reference: article)

But how can I specify that article_number_1 and article_number_2 both refer to article_number from table article? This is what I thought:

create table related_articles (
    article_number_1 integer,
    article_number_2 integer,
    primary key(article_number_1, article_number_2),
    foreign key(article_number_1) references article,
    foreign key(article_number_1) references article,
)

Now I know those two lines of foreign key are incorrect, but I don't know how to tell to my database that the two attributes point to the same attribute of the table article. Anyone that can help?

Upvotes: 0

Views: 536

Answers (1)

Serge Ballesta
Serge Ballesta

Reputation: 149125

After references, you need a full reference, that is table_name(column_name). In your example it would be something like :

create table related_articles (
    article_number_1 integer,
    article_number_2 integer,
    primary key(article_number_1, article_number_2),
    foreign key(article_number_1) references article(article_number),
    foreign key(article_number_2) references article(article_number),
)

if article_number is the identifier in table article.

Upvotes: 1

Related Questions