Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 ALTER TABLE

Author  Topic 

sigmas
Posting Yak Master

172 Posts

Posted - 2013-07-30 : 09:16:29
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-30 : 09:20:56
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

52326 Posts

Posted - 2013-07-31 : 01:54:39
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
   

- Advertisement -