CODEWITHSUNDEEP
sqlsql-servert-sqlforeign-keys
User987
User987

Reputation: 3823

Changing column to just a regular column instead of foreign key

I have a specific situation where I have a column type in my DB where the column is currently of type int and it is a foreign key type that allows nulls...

My question is: is it possible to change now this column simply to a int type which isn't a foreign key without messing up the data in both tables, and leaving the values intact?

For example:

alter table xy
alter column ForeignKeyIdColumn int null

Would something like this work?

Can someone help me out?

Upvotes: 0

Views: 113

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82474

A foreign key is not a property of the columns of a table, it's a database object that defines the relationship between tables.

You can drop the foreign key constraint (though I wouldn't recommend it) using ALTER TABLE with DROP CONSTRAINT, but you have to know the name of the constraint to do it (this is why it's best practice to name everything in SQL SERVER):

ALTER TABLE xy
    DROP CONSTRAINT <constraint name here>;

However, as I wrote, I wouldn't recommend dropping foreign key constraints.
Foreign keys are the database way to enforce referential integrity - meaning that a value referenced by another table can't be changed or deleted without changing or deleting the referencing value as well.
Dropping foreign keys means your database will not be able to enforce referential integrity any more and that might lead to corrupt data.

For more information, read Delete Foreign Key Relationships

Upvotes: 1

Related Questions