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..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|||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.|||Cool. :cool:
blindman|||Doesn't appear so in RI
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|||Uncool. :confused:
blindman|||Originally posted by rdjabarov
providing that the column allows NULLs.
Nulls...pk...hmmmmmmmmm
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|||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?|||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...
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...|||So I assume it worked the second time, Brett?
Cool? Uncool?|||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..)
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|||PK cannot allow NULLs, so Brett's experiment is not exactly demonstrating what I was talking about.
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|||...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.|||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...|||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?
