SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 ALTER TABLE
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sigmas
Posting Yak Master

Belarus
172 Posts

Posted - 07/30/2013 :  09:16:29  Show Profile  Reply with Quote
I have a table like following, I need to change data type of customer_id column with ALTER TABLE but I do not know the correct syntax for this, can someone to help me?

create table orders
(order_id int not null primary key,
...
...
customer_id int not null
constraint fk_customer
foreign key
references customers (customer_id)
);


Database Development MCTS, MCTIP

James K
Flowing Fount of Yak Knowledge

3742 Posts

Posted - 07/30/2013 :  09:20:56  Show Profile  Reply with Quote
In the absence of the foreign key reference, the alter column statement would be something like this:
ALTER TABLE orders ALTER COLUMN customer_id INT NULL;
You would want to alter columns in both tables to be the same type. If you are going to alter them to incompatible types, you would want to drop the FK, then make the changes, add back the foreign key constraint and then insert/update the data that was formerly incompatible.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/31/2013 :  01:54:39  Show Profile  Reply with Quote
If you are going to alter them to incompatible types, you would want to drop the FK, then make the changes, add back the foreign key constraint

It will allow FK to be setup only if columns are of similar dtataypes. otherwise it will throw the error like

Column <column name> is not the same data type as referencing column
<reference columnname> in foreign key <fk constraint name>

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000