How do I add a foreign key constraint?

pstein · **Joined:** Wed Oct 08, 2008 10:59 am **Posts:** 37

When I look at the DDL for a HSQLDB database then the following works:

alter table MESSAGES
add constraint FK_NEXT_MESSAGE
foreign key (NEXT_MESSAGE_ID)
references MESSAGES;

How would the same DDL command look like for a MySQL database ?
How can I add a foreign key constraint?

Peter

gonzao_diaz · **Joined:** Mon Nov 26, 2007 2:29 pm **Posts:** 443

Code:

    <class name="Message" table="MESSAGES">
 
       ...

      <many-to-one name="next" class="Message"
           unique="true"
           column="NEXT_MESSAGE_ID" 
           foreign-key="FK_NEXT_MESSAGE "  />
    </class>

Notice that:

-the attribute "unique" makes this association one-to-one (and not many-to-one), since a message can have only one next message.

-the foreign key is not created by the foreing-key attribute, it just specifies the name of the FK.

pstein · **Joined:** Wed Oct 08, 2008 10:59 am **Posts:** 37

gonzao_diaz wrote:

Code:

    <class name="Message" table="MESSAGES">
 
       ...

      <many-to-one name="next" class="Message"
           unique="true"
           column="NEXT_MESSAGE_ID" 
           foreign-key="FK_NEXT_MESSAGE "  />
    </class>

Notice that:

-the attribute "unique" makes this association one-to-one (and not many-to-one), since a message can have only one next message.

-the foreign key is not created by the foreing-key attribute, it just specifies the name of the FK.

Is there a "normal" (=non XML) command as well for adding a constraint?

gonzao_diaz · **Joined:** Mon Nov 26, 2007 2:29 pm **Posts:** 443

The foreign-key="FK_NEXT_MESSAGE " and others that you see in the mapping file, only are relevant if you build the DB starting from your mapping file (using the Schema exporter or hibernate.hbm2ddl.auto)

If that is not your case, you use normal SQL DDL, like the one in your previous posting.