0

I am trying to add unique constraint as foreign key by this statement:

ALTER TABLE SOME_TABLE ADD(
CONSTRAINT FK_ID FOREIGN KEY (S_ID) REFERENCES OTHER_TABLE(O_ID) UNIQUE (S_ID)
);

I thought that this statement is correct, but all time I got "missing right parenthesis error". Probably I have wrong order of key words. Could you give me advice how to create an unique constraint ?

I red this issue:

Add a unique constraint of a sql table as foreign key reference to an another sql table

but still I have problem with this.

Community
  • 1
  • 1
Ice
  • 1,783
  • 4
  • 26
  • 52
  • 2
    The `unique (..)` part is invalid. The referenced table needs to have such a constraint, you can't specify that when creating the FK (you also don't need the parentheses around the whole `constraint ..` part) –  Jul 18 '16 at 13:22
  • Are you trying to require that S_ID be unique in table SOME_TABLE? You don't have to do that, and it is generally a sign of poor database design if you need to. O_ID must be unique in OTHER_TABLE, but the foreign key is almost never unique (in the table where it is foreign key, not in the "parent" table where it is primary key). –  Jul 18 '16 at 13:31

1 Answers1

3

First, you don't need parentheses. Second, this is two constraints and you might as well give both names:

ALTER TABLE SOME_TABLE
    ADD CONSTRAINT FK_ID FOREIGN KEY (S_ID) REFERENCES OTHER_TABLE(O_ID);

ALTER TABLE SOME_TABLE
    ADD CONSTRAINT UNQ_ST_S_ID UNIQUE (S_ID);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786