none
Removing a Tab Character from a Primary Key RRS feed

  • Question

  • *****UPDATE*****

    The database is SQL Server, apologies for not mentioning that.

    I am an applications manager Oracle JD Edwards EnterpriseOne.

    The JDE applications for some dumb reason allow you to put tab characters into the primary key.

    We have this condition in one of our master files and though the record is old it is giving us fits.

    I have given the DBA insturctions on how to find these records.

    She tried to update the primary key, using the following statement

    update sbdta.f4108 set iolotn= '123ABC   '

    where iolotn= 123ABC (unique constraint is violated)

    and it gave here a duplicate key constraint message.  It is not a duplicate, it is
    123ABC +Tab.  The database however is ignoring this and treating them as the same value.  Because of our IT auditors the IT department is reluctant to delete an re-enter.  I could do this from the functional side but I want to support them on a datafix however it seems I need to help them through this.

    Thanks


    • Edited by John M1 Wednesday, January 22, 2020 7:43 PM
    Tuesday, January 21, 2020 8:32 PM

All replies

  • In SQL server, you need to make sure if the primary key column is referenced by any foreign keys. If yes, you have to disable them first and then update the primary key column:

    UPDATE [schema].[YourTable]
    SET PKColumn = REPLACE(PKColumn, CHAR(9), '')
    WHERE CHARINDEX(CHAR(9), PKColumn) > 0;

    And then update the foreign key columns and enable them if they exists.


    A Fan of SSIS, SSRS and SSAS

    Tuesday, January 21, 2020 10:08 PM
  • This is an ERP system this could be many tables. We've not found tabs for these records anywhere else.

    IF it were hanging on a referenced key wouldn't the db throw us a message stating that.

    Would the tables show in a log or execution plan?

    Tuesday, January 21, 2020 10:52 PM
  • Hi John

    Is it Oracle or SQL Server database? I cannot reproduce it on SQL Server 

    create table #t (s VARCHAR(10) NOT NULL PRIMARY KEY)

    INSERT INTO #t VALUES ('123ABC'+CHAR(9))

    UPDATE #t
    SET s = REPLACE(s, CHAR(9), '')

    SELECT * FROM #t
    WHERE CHARINDEX(CHAR(9), s) > 0;

    DROP TABLE #t


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, January 22, 2020 5:15 AM
    Moderator
  • Hi John,

    Is this an Oracle database or SQL Server database as Uri mentioned? If it is an Oracle database, I suggest you open a thread in Oracle  database forums, People there will help you more effectively. 
    Hope this could help you.

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, January 22, 2020 6:15 AM
  • SQL Server
    Wednesday, January 22, 2020 7:44 PM
  • SQL Server, the application allows you to do this.  sad but true.

    I came here, as I am trying to help our DBA who can't seem to get past this.  I found the records using a LIKE in the where clause with the Char(9)  that was the easy part.

    Is there a log or something that will identify the depencies?  The execution plan perhaps?

    Wednesday, January 22, 2020 7:48 PM
  • Not clear to what you want to do. It is better to post an example.

    A Fan of SSIS, SSRS and SSAS

    Wednesday, January 22, 2020 9:11 PM
  • This is a SQL Server Database.  I can't easily tell you the precise version but that shouldn't matter too much.

    Problem:  We have tabs in a primary key in a master table.

    Our DBA attempted to do a datafix to remove those tabs, but received a message saying there was constraint due to dependency to a foreign key.

    Most help says to remove the constraints from the dependent files, change the data then replace the constraint.

    The JDE system has 3000 tables.  This particular one maybe has less then 10 tables with related data.

    We know how to do the update, but get the error regarding constraints.  IS there a tool in SQL Server that will tell me over which tables the error is being thrown?

    Wednesday, January 22, 2020 10:16 PM
  • This is a SQL server database.

    What you are proposing is similar to what I offered the DBA.

    so I understand

    Create a new table #t, insert the bad records from the other table

    Not sure about the last three statements

    UPDATE #t
    SET s = REPLACE(s, CHAR(9), '') (replace tab with blank?)

    SELECT * FROM #t (there is no update)
    WHERE CHARINDEX(CHAR(9), s) > 0;

    DROP TABLE #t  (drop the temp table)

    Wednesday, January 22, 2020 10:22 PM
  • IF it were hanging on a referenced key wouldn't the db throw us a message stating that.

    Yes, if the update would cause an FK violation, you would get error 547, i.e. constraint violation.

    But did you not say that you got a violation of a unique constraint? Your update statement was not exactly clear, but it could be that there is both '123\t'  and '123'. If you tried to change '123\t' to '123' that will of course fail. If you tried to change it to '123    ' (with trailing blanks), that will still clash '123', since trailing blanks are not significant in SQL.

    Would the tables show in a log or execution plan?

    You can find foreign key references for sys.foreign_keys and sys.foreign_key_columns, but it is not clear if we have to do there.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, January 22, 2020 10:27 PM
  • John

    I would suggest to take one table and check if there is foreign key to others tables , then you can try my approach and if it is FK then you need to update all child tables as well.


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, January 23, 2020 4:46 AM
    Moderator
  • Erland,

    Thank you for your reply.

    I tried to post the image but I need a different kind of account.

    She is using an Oracle SQL Developer Client and SSMS.  Not sure why, so that may be a part of the problem.

    Error Report

    SQL Error: ORA-00001: unique constraint (SBDTA.FXXXX) violated

    00001. 00000 - "unique constraint (%s.%s) violated

    Cause: An update or insert attempted to insert a duplicate key

    For trusted oracle configured DBMS MAC mode , you may see this message if a duplicate entry exists at a different level

    Action: either remove the unique restriction of do not insert the key

    According to Microsoft having the tab will be acceptable to the database but it will work as if it isn't there.  Someone probably added the record using a cut and paste.

    Thursday, January 23, 2020 3:28 PM
  • Error Report

    SQL Error: ORA-00001: unique constraint (SBDTA.FXXXX) violated
    00001. 00000 - "unique constraint (%s.%s) violated

    If the error message goes ORA-nnnnn this is something that takes place on Oracle, no matter what you say.

    And the error message makes it clear that this is a matter of something not being unique; it has nothing to do with references.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, January 23, 2020 10:46 PM