IDENTITY_INSERT = ON required?

IDENTITY_INSERT = ON required?

csdatumcsdatum Posts: 30Questions: 10Answers: 0
edited March 2017 in Free community support

I ran into the following sql error when editing a joined table:

Cannot insert explicit value for identity column in table 'columnName' when IDENTITY_INSERT is set to OFF.

Is there any way to leave IDENTITY_INSERT off without breaking datatables?

This question has accepted answers - jump to:

Answers

  • csdatumcsdatum Posts: 30Questions: 10Answers: 0
    edited March 2017

    I understand this is likely used to populate the foreign key of the primary table, but couldn't the JOINed table entry be created, and an OUTPUT clause be used to insert the foreign key in the primary table? That would deal with DataTables internally though.

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Editor will actually insert into the main table first and then can optionally insert into the joined tables (allowing it to include the newly generated primary key value from the main table). It sounds like you want the inverse of that. Is that correct?

    Allan

  • csdatumcsdatum Posts: 30Questions: 10Answers: 0

    Right. That way the auto-generated PK's in the joined tables are used... and we never have to be setting primary keys where they should be auto-generated by SQL.

    If not, DataTables should at least run "SET IDENTITY_INSERT tableName ON" before setting a joined table, and "SET IDENTITY_INSERT Genre OFF" after each joined table.

    If DataTables is doing neither of these things, what is the preferred way to overcome this problem?

    Many thanks for your time.

  • csdatumcsdatum Posts: 30Questions: 10Answers: 0
    edited March 2017

    Similarly, when I try to update an existing joined entry, I get the sql error "Cannot update identity column 'ID'."
    (where 'ID' is my primary key and identity column.)

  • csdatumcsdatum Posts: 30Questions: 10Answers: 0

    Additionally, inserting the FK in the main table before the PK in the joined table violates any foreign key constraints.

  • csdatumcsdatum Posts: 30Questions: 10Answers: 0

    My main table is called "contacts", and has the fields:

        [ID] int IDENTITY PRIMARY KEY,
        [name] [varchar](500) NULL,
        [phone] varchar(13)
        [loc] [int] NULL FOREIGN KEY REFERENCES locations([ID]),
    

    The joined table is called "locations" and has the fields:

        [ID] int IDENTITY PRIMARY KEY,
        [lat] decimal(10,7) NULL,
        [long] decimal(10,7) NULL,
        [addr1] varchar(1024) NULL,
        [addr2] varchar(1024) NULL,
        [city] [varchar](255) NULL,
        [state] varchar(40) NULL,
        [zip] varchar(10) NULL
    
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    Answer ✓

    There isn't currently a good way to overcome this with the Editor server-side libraries I'm afraid. As I say, they will insert into the main table first, then the joined tables, so it isn't really possible to create a joined row and then reference it in the main table (as you say it would violate rules - and wouldn't actually be possible if the id was auto generated anyway). It requires that the information already exists in the joined tables I'm afraid to say.

    Thanks,
    Allan

  • csdatumcsdatum Posts: 30Questions: 10Answers: 0

    This appears to be the same design pattern used in the Editor join example.

    Isn't this the design pattern of joined tables that Editor is designed to handle? If not, what is?

    Thank you again for your time and consideration.

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    The difference is that the join example doesn't insert into the joined table - only the host table (users in that example). The host table references the join.

    Editor does have the ability to insert into the joined table, but only after the row in the main table has been created or updated. Correct me if I am wrong please, but it sounds like you need to insert into your joined table and then reference that from the main table. It is that first part that Editor doesn't currently support.

    Allan

  • csdatumcsdatum Posts: 30Questions: 10Answers: 0

    Understood.

    So if I were only editing the child table(locations), it would work because no keys are being created

    Or if I had my FK in the child table(locations) it would work... because the parent table(contacts) gets created, and its auto-generated ID would be used to fill the FK in the child table (locations).

    I suppose this means that many-to-many relationships via associative tables are not supported by datatables as well, correct? Because there's no real parent/child relationship. You would have to create both entries before linking them in the associative table.

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    Answer ✓

    I suppose this means that many-to-many relationships via associative tables are not supported by datatables as well, correct?

    They are using the Mjoin class: docs. However it typically assumes that a link table is used to do the one-to-many join - that is certainly the safest way!

    Allan

  • csdatumcsdatum Posts: 30Questions: 10Answers: 0

    Beautiful!

    That helps a lot, thank you!

This discussion has been closed.