Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2006
    Posts
    58

    Question Unanswered: How to alter column length of the master table along with the slave table?

    In SQL Server 2005,here are two tables, created by the following SQL Statements:

    CREATE TABLE student(
    ID CHAR(6) PRIMARY KEY,
    NAME VARCHAR(10),
    AGE INT
    );

    CREATE TABLE score(
    ID CHAR(6) PRIMARY KEY,
    SCORE INT,
    FOREIGN KEY(ID) REFERENCES student(ID)
    );

    For the length of Column ID is not enough, So I want to alter its length.The alter statement is:

    ALTER TABLE student ALTER COLUMN ID CHAR(20)

    For the table student is referenced by table score, the alter statement can not alter the column of the table student, and the SQL Server DBMS give the errors.

    But, I can manually alter the length of the column ID in SQL SERVER Management Studio. How to alter column length of the master table(student) along with the slave table(score)?

    Thanks!

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I assume the error you are talking about has to do with a foriegn key constraint. drop the constraint alter your columns and reapply your constraint.

    BTW, it may just be a long held bias of mine, but numbers make better keys than characters.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    I guess this is due to the CHAR datatype. Values in CHAR columns are padded to the length specified in the definition (in your case 6 characters). Now if you change the definition to CHAR(20) all values are padded to 20 characters and thus modified. But as it is a new value the foreign key constraint is violated because the other table still as the old value padded to 6 characters.

    Why don't you use VARCHAR? It will not also solve this problem, but will use less space as well.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •