Hi all,
I am doing some stuff using NHibernate for which I require your help.

I have 3 tables Program, Course and ProgramCourseLanguage. The ProgramCourseLanguage has a field called "LanguageId" which acts a foreign key from the "LanguageMaster" table.

LOGIC:

Case I:A Program can have multiple course and each course belong to multiple language(s)

Case II:A Program can belong to multiple languages. (No Courses)

The Primary key(Id) of the Program table as well as the Course table act as a Foreign Key in ProgramCourseLanguage table as "ProgramId" and "CourseId" fields respectively.

Either a Program or a course must have languages. A language cannot be left blank.

When I try to edit a Program Name (in case II) keeping the languages unchanged, and try to save I am getting an error "Violation of unique key constraint. Cannot insert duplicate key in the ProgramCourseLanguage table." The ProgramCourseLanguage has a unique key defined in the combination of "ProgramId" + "CourseId" + "LanguageId".

I am trying to achieve this using Cascade.
Can anybody please help me as to how to achieve this scenario.

Pratik,

I am a little confused by your exposition of the problem.
You say your application has 3 tables, but you refer to at least 4.
Also, it is unclear to what parents exactly the entity "Language" would relate.
Could you post the structure of all the tables involved, the mapping file(s) and a simple code snippet with what you are trying to achieve?

Hi,
Actually, the application has many tables, but the scenario what I want to achieve has only 4 tables (the 4th table is just palying a role when it comes to primary - foreign key relationships)

I have 3 tables which are playing a major role
(1) QuickSkillProgramMaster,

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="Richardson.IOPS.Domain" namespace="Richardson.IOPS.Domain">
<class name="QuickSkillProgramMaster" table="QuickSkillProgramMaster">

<id name="Id" column="Id" type="Int32" unsaved-value="0">
<generator class="native"/>
</id>
<bag name="QuickSkillCourseLanguageQuickSkillsProgramList" inverse="true" lazy="true" cascade="all-delete-orphan">
<key column="QuickSkillsProgramId" />
<one-to-many class="QuickSkillCourseLanguage" />
</bag>
<bag name="QuickSkillCourseMasterQuickSkillProgramList" inverse="true" lazy="true" cascade="all-delete-orphan">
<key column="QuickSkillProgramId" />
<one-to-many class="QuickSkillCourseMaster" />
</bag>
<bag name="QuickSkillProgramItemQuickSkillProgramList" inverse="true" lazy="true" >
<key column="QuickSkillProgramId" />
<one-to-many class="QuickSkillProgramItem" />
</bag>
<property column="ProgramName" type="String" name="ProgramName" not-null="true" length="200" />
<property column="IsAnEnrichmentProgram" type="Boolean" name="IsAnEnrichmentProgram" not-null="true" />
</class>
</hibernate-mapping>

2) QuickSkillCourseMaster
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="Richardson.IOPS.Domain" namespace="Richardson.IOPS.Domain">
<class name="QuickSkillCourseMaster" table="QuickSkillCourseMaster">

<id name="Id" column="Id" type="Int32" unsaved-value="0">
<generator class="native"/>
</id>
<bag name="QuickSkillCourseItemQuickSkillCourseList" inverse="true" lazy="true">
<key column="QuickSkillCourseId" />
<one-to-many class="QuickSkillCourseItem" />
</bag>
<bag name="QuickSkillCourseLanguageQuickSkillsCourseList" inverse="true" lazy="true" cascade="all-delete-orphan" >
<key column="QuickSkillsCourseId" />
<one-to-many class="QuickSkillCourseLanguage" />
</bag>
<property column="CourseName" type="String" name="CourseName" not-null="true" length="200" />
<many-to-one name="QuickSkillProgram" column="QuickSkillProgramId" class="QuickSkillProgramMaster" />
</class>
</hibernate-mapping>


3) QuickSkillCourseLanguage.
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="Richardson.IOPS.Domain" namespace="Richardson.IOPS.Domain">
<class name="QuickSkillCourseLanguage" table="QuickSkillCourseLanguage">

<id name="Id" column="Id" type="Int32" unsaved-value="0">
<generator class="native"/>
</id>
<many-to-one name="QuickSkillsProgram" column="QuickSkillsProgramId" class="QuickSkillProgramMaster" />
<many-to-one name="QuickSkillsCourse" column="QuickSkillsCourseId" class="QuickSkillCourseMaster" />
<many-to-one name="Language" column="LanguageId" class="LanguageMaster" />
</class>
</hibernate-mapping>

4. LanguageMaster
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="Richardson.IOPS.Domain" namespace="Richardson.IOPS.Domain">
<class name="LanguageMaster" table="LanguageMaster">

<id name="Id" column="Id" type="Int32" unsaved-value="0">
<generator class="native"/>
</id>
<bag name="IltTranslationLanguageLanguageMasterList" inverse="true" lazy="true" >
<key column="LanguageMasterId" />
<one-to-many class="IltTranslationLanguage" />
</bag>
<bag name="QuickSkillCourseLanguageLanguageList" inverse="true" lazy="true" >
<key column="LanguageId" />
<one-to-many class="QuickSkillCourseLanguage" />
</bag>
<bag name="TrainingRequestDeliveryLanguageList" inverse="true" lazy="true" >
<key column="DeliveryLanguageId" />
<one-to-many class="TrainingRequest" />
</bag>
<property column="LanguageName" type="String" name="LanguageName" not-null="true" length="100" />
<property column="Code" type="String" name="Code" length="5" />
<property column="ForDelivery" type="Boolean" name="ForDelivery" not-null="true" />
<property column="ForQuickSkill" type="Boolean" name="ForQuickSkill" not-null="true" />
<property column="Status" type="Boolean" name="Status" />
<property column="SortIndex" type="Int32" name="SortIndex" />

</class>
</hibernate-mapping>

The entire scenario has been already mentioned in my previous thread and hence I am not re - pasting it here just to avoid mess ups.

I hope this information is fine for you. Please let me know if you require anything else in this context.

Pratik,

I am sorry, but you are still not telling what does the ProgramCourseLanguage table do, which is precisely the one throwing the error.
Could you explain, in simple terms, what entity "has many" of what other entities, and in which scenario you get the error posted?

Hi,
First of all there is no table called ProgramCourseLanguage. In fact, its name is QuickSkillCourseLanguage. This table stores has references from 3 tables viz.,
1) QuickSkillProgramMaster
2) QuickSkillCourseMaster
3) LanguageMaster

All the ID columns of the above three tables are referred as foreign keys in QuickSkillCourseLanguage table.
I have already attached the hbm.xml files for all the above tables in my previous thread.

REQUIREMENTS

Case I:
One Program can have multiple courses and each of this course can belong to multiple language(s). By this I mean, that one ProgramId (say Program 'A') has two courses (say 'B', and 'C') and each of these courses are published in 2 languages English and Spanish.
So in this case I will have following entries in my respective table:
a) QuickSkillProgramMaster table :
One entry (for Program A)
b) QuickSkillCourseMaster table :
Two entries (for Course B and C with Program 'A' actign as a
Foreign key)
c) QuickSkillCourseLanguage table :
Four entries (2 languages(English and Spanish) each for two
Courses (B and C). In this case, the Courses B and C will act as a
Foreign key in QuickSkillCourseLanguage table and the value will be
stored in the QuickSkillsCourseId column. In this case, the
QuickSkillsProgramId column in QuickSkillCourseLanguage table will
have a null value.

Case II:
A Program can DIRECTLY belong to multiple languages. (No Courses)
By this I mean, that one ProgramId (say Program 'A') is DIRECTLY published in 2 languages English and Spanish. NO COURSES INVOLVED.
So in this case I will have following entries in my respective table:
a) QuickSkillProgramMaster table :
One entry (for Program A)
b) QuickSkillCourseLanguage table :
Two entries [2 languages(English and Spanish) each for
Program A]. In this case, the Program A will act as a Foreign key in
QuickSkillCourseLanguage table and the value will be
stored in the QuickSkillsProgramId column. The QuickSkillsCourseId
column in QuickSkillCourseLanguage table will have null values in this
case

Either a Program or a course must have languages. A language cannot be left blank.

The issue that I am facing

When I try to edit a Program Name (in case II) keeping the languages unchanged, and try to save I am getting an error "Violation of unique key constraint. Cannot insert duplicate key in the QuickSkillCourseLanguage table." The QuickSkillCourseLanguage has a unique key defined in the combination of "QuickSkillsProgramId" + "QuickSkillsCourseId" + "LanguageId".

I am trying to achieve this using Cascade.
I hope this information fully explains everything

Plese let me know, if you still have any issues pertaining to this context