SQL Query issue while trying to do relations

Question

i am creating tables in my database and i have a weird problem going on with my query when trying to create a relation between tables.

here is my query :

CREATE  TABLE ogrnizationarticle (
  OAID Int NOT NULL ,
  _Text VARCHAR(255) NOT NULL ,
  ARank Int NULL DEFAULT NULL ,
  Acomment VARCHAR(255) NULL DEFAULT NULL ,
  Author VARCHAR(45) NULL DEFAULT NULL ,
  PRIMARY KEY (OAID, _Text) ,
  foreign key (OAID) references organization(OID),
  );
-- -----------------------------------------------------
-- Table nasshope.organization_Article_comment
-- -----------------------------------------------------
CREATE  TABLE organizationArticleComment (
  O_Article_ID Int NOT NULL ,
  Comment VARCHAR(255) NOT NULL ,
  Article VARCHAR(255) NOT NULL ,
  Comment_Like Int NULL DEFAULT NULL ,
  _Date DATETIME NULL DEFAULT NULL ,
  PRIMARY KEY (O_Article_ID, Comment, Article) ,
  foreign key (O_Article_ID) references ogrnizationarticle(OAID),
  );

And here is the error

Msg 1776, Level 16, State 0, Line 13
There are no primary or candidate keys in the referenced table 'ogrnizationarticle' that match the referencing column list in the foreign key 'FK__organizat__O_Art__42ACE4D4'.
Msg 1750, Level 16, State 0, Line 13
Could not create constraint. See previous errors.

You need a key in `ogrnizationarticle` that matches the foerign key but your primary key in `ogrnizationarticle` has two columns. — madth3, Mar 12 '13 at 22:46

Martin Smith · Answer 1 · 2013-03-12T22:54:25.987

For this to work you would need to add the _Text column to organizationArticleComment and use

foreign key (O_Article_ID,_Text) references organization(OAID,_Text)

But I don't suggest you do that

OAID,_Text doesn't seem to me to be a good primary key for ogrnizationarticle. I would use a surrogate PK (IDENTITY) there and then you just need to include that in organizationArticleComment.

Otherwise you will end up needing to duplicate across tables a key that is potentially both quite wide and unstable. As the PK is also the clustered index on that table you should be aware that the clustered index key is also included in any non clustered indexes on the table too.

@axrwkr - Yep, Good point. The OP should really just pick one name and stick with it. Rather than `O_Article_ID` vs `OID` vs `OAID`. And fix the misspellings of `ogrnization` whilst they are at it! — Martin Smith, Mar 12 '13 at 22:39

score 0 · Answer 2 · answered Mar 12 '13 at 22:32

0

OAID field in the ogrnizationarticle must be either `unique key or primary key.

See Candidate key for more information.

answered Mar 12 '13 at 22:32

Hamlet Hakobyan

32,965
6
52
68

SQL Query issue while trying to do relations

2 Answers2