Hello, I'm very new to O/R mapping and somewhat new to .NET 2. I'm using LLBL v.2. I have a heirarchy such as this: Person > User. LLBL see's this as a TargetPerEntity inheritance in the design view. Person has one pk called 'Id', User has a column 'Id' as a foreign key to Person.Id. In Sql, User.Id is a primary key but is not an identiy column so as to allow inserts on this field.

I am trying to insert once into User and let LLBL take care of adding the needed record into Person and then User.

UserEntity c = new UserEntity();
c.Fname = "a"; // Person
c.Lname = "b"; // Person
c.UserName = "c"; // User
...
c.Save();

When I run this code, I get this exception: An exception was caught during the execution of an action query: Cannot insert the value NULL into column 'Id', table 'Tracker.dbo.User'; column does not allow nulls. INSERT fails. The statement has been terminated.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

Maybe I'm way off? Thanks for your help!

Is Person.Id an identity column? If not, you have to specify a value for Id.

If I make Person.Id and identity column, I get the following error:

An exception was caught during the execution of an action query: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_User_Person". The conflict occurred in database "Tracker", table "dbo.Person", column 'Id'. The statement has been terminated.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

Normally I design databases so that each table has its own unique primary key, and any foreign keys are additional columns. So in this case, the foreign key 'id' would be an additional column on the table User.

From what I understand from reading the LLBL help docs, I should just have one id column for User which is also the foreign key to Person. Is that correct? I must be misunderstanding something. Thanks for your help!

brent wrote:

If I make Person.Id and identity column, I get the following error:

An exception was caught during the execution of an action query: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_User_Person". The conflict occurred in database "Tracker", table "dbo.Person", column 'Id'. The statement has been terminated.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

Normally I design databases so that each table has its own unique primary key, and any foreign keys are additional columns. So in this case, the foreign key 'id' would be an additional column on the table User.

That's not correct: to model a hierarchy in tables, you have to make the PK of the subtype an FK to the PK of the supertype, so user's PK has to be the FK to the PK of Person.

Thank you for your help. I've set it up the way you've suggested but I still the FK constraint error. Is this because the records are being inserted in the wrong order? (I've actually tried it with User as the Primary key table and also User and Foreign key table, but I get the same results either way.)

If I turn off enforce foreign key constraints in sql it inserts with no errors but the inserted User.Id differs from the inserted Person.Id.

Thanks again for your help.

brent wrote:

Thank you for your help. I've set it up the way you've suggested but I still the FK constraint error. Is this because the records are being inserted in the wrong order? (I've actually tried it with User as the Primary key table and also User and Foreign key table, but I get the same results either way.)

If I turn off enforce foreign key constraints in sql it inserts with no errors but the inserted User.Id differs from the inserted Person.Id.

Thanks again for your help.

I think I don't have a clear picture what the situation is of your table structure and entities mapped onto it. Could you give the table DDL SQL for person and user and between which fields an FK constraint is defined (and which fields are PK and identity)? I assume your code snippet in the first post is the piece of code which you use to save the user which fails.

When you save a User entity, you don't need to save a person entity as well, as a user is-a person, due to the inheritance. The logic internally finds out which row to insert first, that's all been taken care of, including syncing between fields. IF the tables are defined correctly of course.

Thanks again for your help! I decided to build a new database with just this relationship in question, wired everything up, ran it, and it worked perfectly . Somehow my old database must have glitched or who knows. At least I can stop pulling out hair

These moments are so bittersweet.

Otis wrote:

To model a hierarchy in tables, you have to make the PK of the subtype an FK to the PK of the supertype, so user's PK has to be the FK to the PK of Person.

I am having trouble with this too. Should the subtype PK be an identity column, or just the supertype?

If I don't set the identity on both tables, the insert fails because it doesn't use the supertype's identity when inserting into the subtype.

I see in the LLBL designer that the subtype's PK column has SCOPE_IDENTITY() for sequence name, but in the query debug output nothing is generated for the PK column on the subtype's insert.

benles wrote:

Otis wrote:

To model a hierarchy in tables, you have to make the PK of the subtype an FK to the PK of the supertype, so user's PK has to be the FK to the PK of Person.

I am having trouble with this too. Should the subtype PK be an identity column, or just the supertype?

it can't be an identity column, as it's an FK, so it gets its value from the PK field it references to

If I don't set the identity on both tables, the insert fails because it doesn't use the supertype's identity when inserting into the subtype.

You should make the PK of the root of the hierarchy an identity.

Otis wrote:

benles wrote:

Otis wrote:

To model a hierarchy in tables, you have to make the PK of the subtype an FK to the PK of the supertype, so user's PK has to be the FK to the PK of Person.

I am having trouble with this too. Should the subtype PK be an identity column, or just the supertype?

it can't be an identity column, as it's an FK, so it gets its value from the PK field it references to

If I don't set the identity on both tables, the insert fails because it doesn't use the supertype's identity when inserting into the subtype.

You should make the PK of the root of the hierarchy an identity.

This makes sense. I think there might be a bug in the LLBL designer where removing an identity specification from a column does not refresh correctly. Still investigating, but hierarchies are working now.

There's indeed a bug, I explained that in the other thread you replied to with the same issue .