Results 1 to 15 of 15
  1. #1
    Join Date
    Nov 2003
    Posts
    24

    Unanswered: can't convert char to varchar

    I upgraded my datbase from 6.5 to 2000. Now I have primary key in one table datatype CHAR(5) and second table with a column having datatype VARCHAR(5).

    I am trying to create a foreign key from second to first table but I am getting following error.

    "Column 'dbo.dma.dma' is not the same data type as referencing column 'fone.dma' in foreign key 'FK_fone_dma'."


    But I beleive CHAR to VARCHAR conversion is implicit.

    COuld anyone please enlighten this problem.

    Thanks in advance..

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The conversion is implicit, but the server won't do conversions for foreign keys. The two must be the same datatype.

    You can choose to maintain relational integrity through the use of triggers, but you are better off making the field types uniform.

    blindman

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    If the table was created with ANSI_PADDING OFF, then you can drop the foreign key, alter table alter column <col_name> char(5) NULL + any defaults or check constraints. With ANSI_PADDING OFF the way data is stored in CHAR datatype is the same as for VARCHAR, providing that the column allows NULLs. After you altered the column re-create your foreign key and you're done.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Cool.

    blindman

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Doesn't appear so in RI

    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable99 (Col1 char(5) NOT NULL PRIMARY KEY, Col2 int DEFAULT 0)
    GO
    
    CREATE TABLE myTablexx (Col1 char(5) NOT NULL PRIMARY KEY, Col2 int DEFAULT 0)
    ALTER TABLE myTablexx ADD CONSTRAINT myTablexx_FK1 FOREIGN KEY (Col1) REFERENCES myTable99 (Col1)
    GO
    
    CREATE TABLE myTable00 (Col1 varchar(5) NOT NULL PRIMARY KEY, Col2 int DEFAULT 0)
    ALTER TABLE myTable00 ADD CONSTRAINT myTable00_FK1 FOREIGN KEY (Col1) REFERENCES myTable99 (Col1)
    GO
    
    DROP TABLE myTablexx
    DROP TABLE myTable00
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Uncool.
    blindman

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by rdjabarov
    providing that the column allows NULLs.
    Nulls...pk...hmmmmmmmmm

    Code:
    
    USE Northwind
    GO
    
    CREATE TABLE myTable99 (Col1 char(5) NOT NULL PRIMARY KEY, Col2 int DEFAULT 0)
    GO
    
    CREATE TABLE myTablexx (Col1 char(5) NOT NULL PRIMARY KEY, Col2 int DEFAULT 0)
    ALTER TABLE myTablexx ADD CONSTRAINT myTablexx_FK1 FOREIGN KEY (Col1) REFERENCES myTable99 (Col1)
    GO
    
    CREATE TABLE myTable00 (Col1 varchar(5) NOT NULL CONSTRAINT myTable00_PK PRIMARY KEY, Col2 int DEFAULT 0)
    -- Will Fail
    ALTER TABLE myTable00 ADD CONSTRAINT myTable00_FK1 FOREIGN KEY (Col1) REFERENCES myTable99 (Col1)
    ALTER TABLE myTable00 ADD Col3 char(5) NULL
    UPDATE myTable00 SET Col3 = Col1
    ALTER TABLE myTable00 DROP CONSTRAINT myTable00_PK
    ALTER TABLE myTable00 DROP COLUMN Col1
    ALTER TABLE myTable00 ADD Col1 char(5) NULL
    UPDATE myTable00 SET Col1 = Col3
    ALTER TABLE myTable00 ALTER COLUMN Col1 char(5) NOT NULL
    ALTER TABLE myTable00 ADD CONSTRAINT myTable00_PK PRIMARY KEY (Col1)
    -- Will NOT Fail
    ALTER TABLE myTable00 ADD CONSTRAINT myTable00_FK1 FOREIGN KEY (Col1) REFERENCES myTable99 (Col1)
    GO
    
    DROP TABLE myTablexx
    DROP TABLE myTable00
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Nov 2003
    Posts
    24
    Originally posted by rdjabarov
    If the table was created with ANSI_PADDING OFF, then you can drop the foreign key, alter table alter column <col_name> char(5) NULL + any defaults or check constraints. With ANSI_PADDING OFF the way data is stored in CHAR datatype is the same as for VARCHAR, providing that the column allows NULLs. After you altered the column re-create your foreign key and you're done.
    Thanks.

    But the same structure (PK CHAR / FK VARCHAR) is working fine in existing DB of 6.5.
    How???

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Good old M$....

    They forgot to forward engineer that "feature"...

    Hey I still think that this is wrong....had lots of disagreements about it though...

    Code:
    USE Northwind
    GO
    
    DECLARE @x int
    CREATE TABLE myTable99(Col1 datetime)
    SELECT @x = 0
    INSERT INTO myTable99(Col1) SELECT @x
    SELECT * FROM myTable99
    GO
    
    DROP TABLE myTable99
    GO
    In DB2, Oracle, ect, that breaks the rules...SQL Server loosley defines that 0 is a valid datatype for datetime...blew my mind when I found this out...

    Oh, and btw, if it's a PK, you won't be able to do the ANSI thing (I don't thin)

    If you can, post a sample...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    So I assume it worked the second time, Brett?

    Cool? Uncool?

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    If you mean the second alter, yes it works...cut and paste it in to QA and watch the test...

    THE ANSI_PADDINGS thing I'll have to play with it...

    But since a PK can't be NULL the point is probably moot...

    You know what a faster way would be (but probably more resource intensive..)

    Code:
    CREATE TABLE myTable007 (Col1 char(5) NOT NULL CONSTRAINT myTable007_PK PRIMARY KEY, Col2 int DEFAULT 0)
    INSERT INTO myTable007 (Col1, Col2) SELECT Col1, Col2 FROM myTable00
    DROP TABLE myTable00
    SELECT Col1, Col2 INTO myTable00 FROM myTable007
    DROP TABLE myTable007
    ALTER TABLE myTable00 ADD CONSTRAINT myTable00_FK1 FOREIGN KEY (Col1) REFERENCES myTable99 (Col1)
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    PK cannot allow NULLs, so Brett's experiment is not exactly demonstrating what I was talking about.

    Code:
    set ansi_padding off
    go
    create table parent (f1 char(5) not null primary key, f2 int null)
    go
    create table child (f1 char(5) null, f2 int null)
    go
    alter table child add constraint fk_child2parent foreign key  (f1) references parent(f1)
    go

  13. #13
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    ...And if you insert 'A' into PARENT and CHILD tables and select DATALENGTH(f1) from both, you'd see that the result on PARENT is 5 while on CHILD is 1. Thus, the behavior of VARCHAR datatype in CHILD table.

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: can't convert char to varchar

    Originally posted by nmajeed
    I upgraded my datbase from 6.5 to 2000. Now I have primary key in one table datatype CHAR(5) and second table with a column having datatype VARCHAR(5).
    But that was the original question....

    PK cannot allow NULLs, so Brett's experiment is not exactly demonstrating what I was talking about.
    But that's neat...

    EDIT: But I rarely play with settings, because it can be dangerous...you have to remeber that you have a particular setting, and code for it...

    If I do change a setting, it's always in the context of a transacxtion, and is set back at the conclusion...
    Last edited by Brett Kaiser; 12-17-03 at 13:34.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  15. #15
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    So what are your settings in the QA when you're about to create a table, for example? Or you're creating tables in EM?

Posting Permissions

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