-1

I have 2 tables :

create table testContent
(
     ID_testContent int not null constraint PK_testContent primary key,
     name nchar(10),
     family nchar(10),
     ID_Image int not null UNIQUE   
)

create table ImageSources
(
    IDN_Image int not null constraint PK_ImageSources primary key,
    URL_IMAGE nchar(10)
)

Which one is correct ? And why ?

 ALTER TABLE Jb_MainDB.dbo.testContent
    ADD CONSTRAINT testContent_ImageSources_IDN_Image_fk
    FOREIGN KEY (ID_Image) REFERENCES ImageSources (IDN_Image)

or

 ALTER TABLE Jb_MainDB.dbo.ImageSources
    ADD CONSTRAINT ImageSources_testContent_ID_Image_fk
    FOREIGN KEY (IDN_Image) REFERENCES testContent (ID_Image)
1

Given your tables:

ALTER TABLE Jb_MainDB.dbo.ImageSources
    ADD CONSTRAINT ImageSources_testContent_ID_Image_fk
    FOREIGN KEY (IDN_Image) REFERENCES testContent (ID_Image)

is invalid since ID_Image is not UNIQUE in testContent. Assume that you could declare a foreign key like that and that your content looked like:

insert into testContent (ID_testContent, ID_Image)
values (1,1), (2,1);

insert into ImageSources (IDN_Image)
values (1);

Would:

delete from testContent where ID_testContent = 1

be allowed? One could easily imagine a theory where this would be allowed until the last tuple with ID_Image = 1 is deleted, but SQL is not defined like that. You can only reference a column that is declared as primary key or as a unique constraint. If the key is a composite key (contains several columns), all columns must be referenced. The correct foreign key is therefore:

ALTER TABLE Jb_MainDB.dbo.testContent
    ADD CONSTRAINT testContent_ImageSources_IDN_Image_fk
    FOREIGN KEY (ID_Image) REFERENCES ImageSources (IDN_Image)

EDIT: after a unique constraint is added in the OP it changes the scenario completely.

Both f.k. are now valid, it's more a question of what you want to express. Can testcontent exist without an imagesource, make that the PARENT table:

ALTER TABLE Jb_MainDB.dbo.ImageSources
    ADD CONSTRAINT ImageSources_testContent_ID_Image_fk
    FOREIGN KEY (IDN_Image) REFERENCES testContent (ID_Image)

If it is the other way around, ImageSources should be the PARENT table:

ALTER TABLE Jb_MainDB.dbo.testContent
    ADD CONSTRAINT testContent_ImageSources_IDN_Image_fk
    FOREIGN KEY (ID_Image) REFERENCES ImageSources (IDN_Image)

If both ImageSources and TestContent can exist independently of each other you can remove ID_Image from testContent and create a table like:

CREATE TABLE testContent_ImageSources
( ID_testContent int not null
      foreign key references testContent (ID_testContent)
, ID_Image int not null
      foreign key references Imagesources (ID_Image)
,    primary key (ID_testContent, ID_Image) 
);
  • thanks. I am editing the table. i'm confused about the direction of defining the FK. It will be define in parent or child? – DanialAbdi Aug 5 '17 at 8:34
  • 2
    Many instances of an FK can reference one instance of a PK. But many instances of a PK cannot be referenced by one instance of an FK. Beyond that, which one is correct depends on the subject matter relationships. You don't design these relationships. You discover them. – Walter Mitty Aug 6 '17 at 19:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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