AdFactum ObjectMapper .NET Blog

Official blog of the AdFactum ObjectMapper .NET

Attribute: [ForeignKey]

Posted by Gerhard Stephan on April 19th, 2007

Beside the automatic generation of foreign keys, it’s sometimes necessary to create foreign keys that are driven by business logic. Those foreign keys can’t be handled automatically, but they have to be set manually with the ForeignKey attribute.

Create a single foreign key relation:

Creating a single foreign key relation is pretty easy. This can be done by attributing the property with the ForeignKey attribute:

        [ForeignKey(typeof(RangeText), "Key")]

        public new string FilterKey

        {

            get { return base.FilterKey; }

            set { base.FilterKey = value; }

        }

 

The resulting DDL script would look like:

alter table RANGE

  add constraint RANGE_FK01 foreign key (FILTERKEY)

  references RANGETEXT (KEY);

Create compound foreign keys using key groups:

In order to create compound foreign keys you have to bundle the affected properties in foreign key groups. A foreign key group owns a number that identifies the group. The number itself isn’t significant, it’s only important that the properties you want to group have the same key group number assigned.

        [ForeignKey(UNIQUE_GROUP, typeof(ManufactoryText), "Key")]

        public new string FilterKey

        {

            get { return base.FilterKey; }

            set { base.FilterKey = value; }

        }

 

        [ForeignKey(UNIQUE_GROUP, typeof(ManufactoryText), "Locale")]

        public new string Locale

        {

            get { return base.Locale; }

            set { base.Locale = value; }

        }

 

The resulting DDL script would look like:

 

alter table MANUFACTORY

  add constraint MANUFACTORY_FK01 foreign key (FILTERKEY, LOCALE)

  references MANUFACTORYTEXT (KEY, LOCALE);

Create compound foreign keys using key groups with sorted keys:

Additional to the foreign key group feature you can sort the keys within the foreign key group. Have a look at the example above. Most times it is necessary to store the properties in a defined sorting. That’s important if you have an compound index on the table you want to use with the compound foreign key.

In that case, the second number, after the foreign key group defines the sorting of the keys. Imagine you want to change the ordering of the keys in our example above.

        [ForeignKey(UNIQUE_GROUP, 2, typeof(ManufactoryText), "Key")]

        public new string FilterKey

        {

            get { return base.FilterKey; }

            set { base.FilterKey = value; }

        }

 

        [ForeignKey(UNIQUE_GROUP, 1, typeof(ManufactoryText), "Locale")]

        public new string Locale

        {

            get { return base.Locale; }

            set { base.Locale = value; }

        }

 

The resulting DDL Script would look like: 

alter table MANUFACTORY

  add constraint MANUFACTORY_FK01 foreign key (LOCALE, FILTERKEY)

  references MANUFACTORYTEXT (LOCALE, KEY);

 

As you can see the sorting of the columns within the foreign key group changed.

Summary:

The foreign key attribute offers you a lot of possibilities to cover more than 90% of all use cases for that you might need foreign key constraints. The last 10% should be better left to a seasoned database admin, because they are such special that they can’t be handled by a generic OR Mapping tool.

Cheers
Gerhard

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>