Need to extend length of PK

  • jdasilva

    Ten Centuries

    Points: 1056

    I have inherited a database, and have found a bit of a flaw that is preventing me from updating it.  Let me first start by saying I know a little about SQL, but mainly from access, and a few searches here and there.

    The problem is that the primary key (for office codes) was set to a length of 5 chars.  We have merged with a new company, and the office naming convention is 6 chars long.  I want to set it to 10 chars, just to be safe.  I am in SQL Server Management Studio, and can see the table, but I cannot make changes to it.  When I try it gives this error:

    Saving changes is not permitted.  The changes you have made require the following tables to be dropped and re-created.  You have either made changes to a table that can’t be re-created or enabled the option Preventing saving changes that require the table to be re-created.

    I can (it seems) to remove the PK marker from that column, and save, and it seems to be ok with that, but after that, I still cannot change the length of the column.  This database is not in constant use, and I can cut the end users off from it for a day.  I just don’t want to wreck the database in trying to get these changes made.

     

  • Lynn Pettis

    SSC Guru

    Points: 442080

    This is an easy issue to correct.  In SSMS click on Tools.  In the drop down menu click Options.  In the dialog box click on Table and Database Desgners.  In Table Options you will find a checkbox selected next to Prevent saving changes that require table re-creation.  Uncheck the box and click the OK button.

     

  • jdasilva

    Ten Centuries

    Points: 1056

    I have just been reading a bit on this, and the possibility of data loss.  This table does not hold a lot of data (about 15 entries), but this table links via this key to all other tables (to organize the data by office).  I’m sure if the data is lost I can re-create it, but would the linkages be damaged by this?  If so, I’m a bit weary to try.  Is it possible in SQL SMS to back up the database to a new temp database, just in case?

    Thanks for the quick reply.

  • Lynn Pettis

    SSC Guru

    Points: 442080

    In Object Explorer you can right click on the database and in the pop-up menu select the option to backup the database.

    Based on what you have just posted, you are going to have to change the length of the column in question every where it is being used in the database.  This is something you may not be able to do in SSMS using the designers.  You are going to have write DDL code to make all the appropriate changes in all the tables affected.  One question I have is the column defined as CHAR(5) or VARCHAR(5)?

     

  • Jeff Moden

    SSC Guru

    Points: 993402

    If they’re not doing regular backups of your databases and transaction log files, you have a serious problem.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • Lynn Pettis

    SSC Guru

    Points: 442080

    Jeff Moden wrote:

    If they're not doing regular backups of your databases and transaction log files, you have a serious problem.

    This too.

     

  • jdasilva

    Ten Centuries

    Points: 1056

    The server is backed up regularly, but I don’t want to have to bother that team if I can do things safely.  I assume I would have to change the length in all tables.  they are nvarchar(5).

    I see this named field in a few of the other tables.  Odd, in one it is nvarchar(7)…  In any case, would this complicate things?  I think in some of the tables, this field is populated from the interface (such as when creating a new staff member, it assigns the office code based on the office currently being viewed).  I would think that this would mean it was an independent field, not truly related…

    OK, I see the option (Tasks>Backup) listed.  I will look up how this specifically works, I want to know what I am doing before I do it.

  • Jeff Moden

    SSC Guru

    Points: 993402

    jdasilva wrote:

    The server is backed up regularly, but I don't want to have to bother that team if I can do things safely.  I assume I would have to change the length in all tables.  they are nvarchar(5). I see this named field in a few of the other tables.  Odd, in one it is nvarchar(7)...  In any case, would this complicate things?  I think in some of the tables, this field is populated from the interface (such as when creating a new staff member, it assigns the office code based on the office currently being viewed).  I would think that this would mean it was an independent field, not truly related... OK, I see the option (Tasks>Backup) listed.  I will look up how this specifically works, I want to know what I am doing before I do it.

    I applaud the idea of you wanting to know more about something before you do it, but some friendly advice might save your bacon…

    If you have another team doing backups, then you shouldn’t be doing any kind of a backup even if a “Copy Only” backup is done.  You don’t know where to store it, you don’t know what the impact will be in many areas (especially concerning disk space), and if something blows up because of your backup or you inadvertently break the Transaction Log Chain, that “Team” will be on you like white on rice.

    In order to keep from bothering the team for such things in the future, have them build a stored procedure for you to run.  Not only will that take the “something went wrong” onus off of you, the script will provide you with the opportunity to learn something on the fly.  It will also convince the team that you’re a team player instead of a cowboy and, these days, folks like us need all the help we can get.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • Lynn Pettis

    SSC Guru

    Points: 442080

    I am sure someone will correct me if I am wrong, but inceasing the size of the NVARCHAR(5) or NVARCHAR(7) columns shouldn’t require a rebuild of the tables if you create the scripts to modify the size of the columns from 5 or 7 to 10.  I can see why SSMS does it that way, it doesn’t think about the change being made and does it the simplest way it knows which is drop and create.

    To test this out, set up a sandbox database and practice writing and running the necessary script to alter (increase) the size of a nvarchar column.  Once you are confident that you can write (without a designer) the necessary scripts you are on your way to being a more knowledgeable T-SQL developer.  Learn to NOT rely on the designers.  They are limited in what they allow you to do in T-SQL and you become better at T-SQL development.  You will also get better familiar with the MS documentation.  Trust me, I have been doing this or over twenty years and I still frequently check the documentation for things I don’t use frequently or the new things I haven’t learned yet.

     

  • Steve Jones – SSC Editor

    SSC Guru

    Points: 713715

    You can issue an ALTER TABLE … ALTER COLUMN without rebuilding the table, even if the column is the PK.

    CREATE TABLE dbo.Document
    (DocumentKey NVARCHAR(5) NOT NULL CONSTRAINT DocumentPK PRIMARY KEY
    , DocumentName NVARCHAR(200)
    , DocumentDate DATETIME2
    )
    GO
    INSERT dbo.Document
    (
    DocumentKey
    , DocumentName
    , DocumentDate
    )
    VALUES
    (N'ABC23', N'Something very interesting', '2019-01-02'),
    (N'QNI43', N'An adventure you admire', '2019-02-02'),
    (N'MNT33', N'Magnets describing life', '2019-03-04'),
    (N'DEF25', N'Time for nothing', '2019-03-12'),
    (N'HIJ54', N'Dreams of the dark', '2019-04-17')
    GO
    SELECT top 10
    *
    FROM dbo.Document AS d
    GO
    ALTER TABLE dbo.Document ALTER COLUMN DocumentKey NVARCHAR(7) NOT NULL
    GO
  • Lynn Pettis

    SSC Guru

    Points: 442080

    Steve Jones - SSC Editor wrote:

    You can issue an ALTER TABLE ... ALTER COLUMN without rebuilding the table, even if the column is the PK.

    CREATE TABLE dbo.Document
    (DocumentKey NVARCHAR(5) NOT NULL CONSTRAINT DocumentPK PRIMARY KEY
    , DocumentName NVARCHAR(200)
    , DocumentDate DATETIME2
    )
    GO
    INSERT dbo.Document
    (
    DocumentKey
    , DocumentName
    , DocumentDate
    )
    VALUES
    (N'ABC23', N'Something very interesting', '2019-01-02'),
    (N'QNI43', N'An adventure you admire', '2019-02-02'),
    (N'MNT33', N'Magnets describing life', '2019-03-04'),
    (N'DEF25', N'Time for nothing', '2019-03-12'),
    (N'HIJ54', N'Dreams of the dark', '2019-04-17')
    GO
    SELECT top 10
    *
    FROM dbo.Document AS d
    GO
    ALTER TABLE dbo.Document ALTER COLUMN DocumentKey NVARCHAR(7) NOT NULL
    GO

    Thank you for the assist on this one Steve.  I am not in a position at the moment to have actually setup a test to verify.

     

  • jdasilva

    Ten Centuries

    Points: 1056

    thanks all for the input.  I have been doing other searches as well (not that I don’t trust the advice here, but trying to get the whole picture before taking the plunge) dealing with this situation.  I have seen much talk of the alter script to change the size.  My problem is I only know the designer, as truthfully (as with most my IT knowledge) I was not formally trained, other than in C++ back in high school.

    I’m good with the scripting, if I know how to initiate it.  I did a few views in the past, and 2 stored procedures, though this was quite a while ago.  To do the alter script, would that be through right clicking on the table and selecting Script Table As > Alter To?  This is greyed out, but I have not turned off the protection as state above yet.

  • Lynn Pettis

    SSC Guru

    Points: 442080

    jdasilva wrote:

    thanks all for the input.  I have been doing other searches as well (not that I don't trust the advice here, but trying to get the whole picture before taking the plunge) dealing with this situation.  I have seen much talk of the alter script to change the size.  My problem is I only know the designer, as truthfully (as with most my IT knowledge) I was not formally trained, other than in C++ back in high school. I'm good with the scripting, if I know how to initiate it.  I did a few views in the past, and 2 stored procedures, though this was quite a while ago.  To do the alter script, would that be through right clicking on the table and selecting Script Table As > Alter To?  This is greyed out, but I have not turned off the protection as state above yet.

    Just going to say it again for emphasis, you need to learn to write SQL code without using the designers.  They will not allow you to make use of the power available in T-SQL.  They are okay for simple queries, but beyond that they will hinder you.

     

  • jdasilva

    Ten Centuries

    Points: 1056

    So how can I then initiate that code?  Sorry for being a bit on the slow take up on this, but again, most of this is new to me.  I can see notes on doing it from the studio, and some notes on doing it from access.  I’m all for stepping out of the designer, I just don’t know where to take that first step.

  • Lynn Pettis

    SSC Guru

    Points: 442080

    Steve provided a very short tutorial up above.  He shows a simple CREATE TABLE script,  inserts data into the table, and then modifies the table to increase the size of the column that is the PK from NVARCHAR(5) to NVARCHAR(7).

     

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply