2

I have two tables:

  1. Article
  2. Subscription

In the Article table I have two columns that make up the primary key: id, sl. In the Subscription table I have a foreign key 'idsl`.

I use this constraint :

constraint FK_idsl 
  foreign key (idsl) references CSS_SubscriptionGroup(id, sl)

But when I run the query, I getting this error:

Number of referencing columns in foreign key differs from number of referenced columns, table X

2

2 Answers 2

5

In Article Table I have two fields that are the primary key: id,sl. In the Subscription Table I have a foreign key 'idsl`

This design is broken - it is apparent that the composite primary key in Article(id, sl) has been mangled into a single compound foreign key in table Subscription. This isn't a good idea.

Instead, you will need to change the design of table Subscription to include separate columns for both id and sl, of the same type as the Article Table, and then create a composite foreign key consisting of both columns, referencing Article in the same order as the primary key, e.g:

CREATE TABLE Article
(
    id INT NOT NULL,
    sl VARCHAR(50) NOT NULL,
    -- Other Columns

    CONSTRAINT PK_Article PRIMARY KEY(id, sl) -- composite primary key
);

CREATE TABLE Subscription
(
    -- Other columns
    id INT NOT NULL, -- Same type as Article.id
    sl VARCHAR(50) NOT NULL, -- Same type as Article.sl

    CONSTRAINT FK_Subscription_Article FOREIGN KEY(id, sl) 
         REFERENCES Article(id, sl) -- Same order as Article PK
);

Edit

One thing to consider here is that by convention a column named table.id or table.tableid should be unique, and is the primary key for the table. However, since table Article requires an additional column sl in the primary key, it implies that id isn't unique.

Sign up to request clarification or add additional context in comments.

Comments

3

correct syntax for relation:

CONSTRAINT FK_OtherTable_ParentTable
FOREIGN KEY(OrderId, CompanyId) REFERENCES dbo.ParentTable(OrderId, CompanyId)

You must try like this:

constraint      FK_idsl     foreign key (id,sl)  references CSS_SubscriptionGroup(id,sl)

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.