October 26, 2012 at 1:26 am
I have to change a datatype of column int to varchar(10).
Alter Table EmployeeLocDtl
Alter column DeptID nvarchar(10)
when I run this this query I got an error -
Msg 5074, Level 16, State 1, Line 2
The object 'FK_EmployeeLocDtl_DepartmentMaster' is dependent on column 'DeptID'.
Msg 4922, Level 16, State 9, Line 2
ALTER TABLE ALTER COLUMN DeptID failed because one or more objects access this column.
Is there any way to correct this?
plz help its urgent
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
October 26, 2012 at 1:40 am
What is Deptid datatype of Primary table.
both column datatype length should same of primary and child .
though try it from design view it may help you.
October 26, 2012 at 2:30 am
Pls check where you created FK_EmployeeLocDtl_DepartmentMaster foreign key table that column data type,master table column data type are same oher wise it will not allow that operarion....
pls follw below steps:
create table STD_MASTER(Stu_id int constraint pf_std primary key,name varchar(20))
insert into STD_MASTER values(1,'ab')
create table STD_SAL(id int identity(1,1),Stu_id int CONSTRAINT fk_productSales_pid FOREIGN KEY REFERENCES STD_MASTER(Stu_id),paid_date date,amount money)
insert into STD_SAL values(2,'2012-09-30',20000)
seep1:
ALTER TABLE STD_MASTER DROP constraint pf_std;
ALTER TABLE STD_SAL DROP constraint fk_productSales_pid;
step2:
alter table STD_MASTER alter column Stu_id varchar(10) not null
alter table STD_SAL alter column Stu_id varchar(10) not null
step2:
ALTER TABLE STD_MASTER add constraint pf_std PRIMARY KEY NONCLUSTERED(Stu_id)
ALTER TABLE STD_SAL ADD CONSTRAINT fk_productSales_pid
FOREIGN KEY (Stu_id) REFERENCES STD_MASTER(Stu_id)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply