Foreign Key Question.

Nov 12, 2009 at 8:54 PM

I'm currently using SQLite as the provider and have a foreign key question.

I have two tables: 

CREATE TABLE [CountryAreaISO3166_1] 
(
	[ID] INTEGER PRIMARY KEY AUTOINCREMENT,
	[Created] TIMESTAMP NOT NULL DEFAULT (DATETIME('NOW')),
	[Updated] TIMESTAMP NOT NULL DEFAULT (DATETIME('NOW')),
	--
	[Name] VARCHAR(10) UNIQUE NOT NULL
);

CREATE TABLE [CountryISO3166_1] 
(
	[ID] INTEGER PRIMARY KEY AUTOINCREMENT,
	[Created] TIMESTAMP NOT NULL DEFAULT (DATETIME('NOW')),
	[Updated] TIMESTAMP NOT NULL DEFAULT (DATETIME('NOW')),
	--
	[Name] VARCHAR(64) UNIQUE NOT NULL, 
	--
	[AreaID] INTEGER DEFAULT 0
		CONSTRAINT fk_CountryISO3166_1_AreaID
		REFERENCES [CountryAreaISO3166_1](ID),	
	--
	FOREIGN KEY (AreaID) REFERENCES [CountryAreaISO3166_1](ID),
);

CREATE TRIGGER fki_CountryISO3166_1_AreaID_CountryAreaISO3166_1_ID
BEFORE INSERT ON [CountryISO3166_1]
FOR EACH ROW BEGIN
  SELECT RAISE(ROLLBACK, 'insert on table "CountryISO3166_1" violates foreign key constraint "fki_CountryISO3166_1_AreaID_CountryAreaISO3166_1_ID"')
  WHERE (SELECT ID FROM CountryAreaISO3166_1 WHERE ID = NEW.AreaID) IS NULL;
END;

C#:
   public abstract class Base : ISelfContainingObject
   {
      private ObjectInfo objectInfo_ = null;

      public Base()
      {
         ID = -1;
         Created = DateTime.Now;
      }
      [Field("ID", AutoNumber = true, Identifier = true, AllowDBNull = false)]
      public long ID { get; set; }

      [Field("Created", AllowDBNull = false, PersistBehaviours = PersistBehaviours.DontUpdate)]
      public DateTime Created { get; set; }

      [Field("Updated", AllowDBNull = false)]
      public DateTime Updated { get; set; }

      #region ISelfContainingObject

      public ObjectInfo ObjectInfo
      {
         get { return (objectInfo_); }
         set { objectInfo_ = value; }
      }

      #endregion // ISelfContainingObject
   }

   [Persistent("CountryAreaISO3166_1")]
   public class CountryArea : Base
   {
      public CountryArea()
         : base()
      {
         Name = string.Empty;
      }

      [Field("Name", AllowDBNull = false)]
      public string Name { get; set; }
   }

   [Persistent("CountryISO3166_1", PoolSize = 16)]
   public sealed class Country : Base
   {
      [Relation("AreaID = ID", PersistRelationship = PersistRelationships.ChildFirst)]
      private ObjectHolder<CountryArea> area_ = new ObjectHolder<CountryArea>();

      public Country()
         : base()
      {
         Name = string.Empty;
      }

      [Field("Name", AllowDBNull = false)]
      public string Name { get; set; }
      // Uncommenting solves the FK exception
      // Not and ideal solution
      //[Field("AreaID")]
      //public int AreaID { get; set; }

      public CountryArea Area
      {
         get { return area_.InnerObject; }
         set { area_.InnerObject = value; }
      }

   }

When doing an import i use the following code:

               Model.CountryArea area = null;

               if (countryCode2CountryArea_.TryGetValue(columns[2], out area) == false)
               {
                  area = defaultArea_;
               }

               Image flag = HttpAgent.FetchImage(columns[0]);

               Model.Country country = new Model.Country { Flag = flag
                                                            , Area = area
                                                            , Name = columns[1]
                                                            , Alpha2 = columns[2]
                                                            , Alpha3 = columns[3]
                                                            , Numeric = Int32.Parse(columns[4])
               };
               storage_.Countries.Add(country);
               storage_.Context.PersistChanges(country)

The ExecuteCommand produces the following trace:

 - Query text: insert into CountryISO3166_1 (Name, Created, Updated) values (@p0, @p1, @p2)

The AreaID is not being inserted, it has been correctly resolved and the 'area' variable is correct. 
This throws an FK constraint error. 

How do I resolve this. If I uncomment the AreaID property then the AreaID is correctly put into the insert statement.
I really don't want to expose the AreaID directly though. Is there any other solution to solve this FK problem?

thanks in advnace




 

 

 

 

 

Dec 9, 2009 at 10:44 PM

I don't think there is anyway to get around that. OPF3 is a separate library and sets the values of the properties/members of your custom classes. If the access modifier is anything but public then OPF3 won't have permission to set the variable. The closest thing you could try is this

[Field("AREAID", AllowDBNull=false)]
public int AreaId
{
	private get{ return _AreaId; }
	set{ _AreaId = value; }
}

Now you can set the variable but you can't retrieve it. This works with an insert just fine.

Coordinator
Dec 11, 2009 at 3:27 AM

mtycholaz is right. You need to expose the AreaId. But you can set the setter private to make sure nobody is able to set it other than Opf3. mtycholaz sample has the error that he sets the getter private but what you really want is the setter (set method) to be private.