Sql-server – Changing Key (PK, FK) properties in DROP/Add column statements

alter-tabledatabase-designddlforeign keysql server

I want to change a table by adding a column. So far nothing to see here, BUT I would like to make this column part of a composite key, i.e., I now have a layout

table_name( Field_1  datatype PK, Field_2 datatype,....)

and I want the inserted column, say Field_k to be part of a PK jointly with the existing single-field PK.

I haven't found any source for how to do this or for whether it is possible or not. Any advice, please?

Best Answer

You need to drop the FKs that reference it, then drop the PK and re-create it, then re-create the FK. There's no magic syntax for this, but you can get some ideas here for how to generate the drop/create scripts for the FKs:

Why dropping all foreign keys from a script is not working?

would I get a warning if I am (unknowingly, of course) violating some constraints in the process, i.e., if I forget to drop some referenced table? Just curious as to how SQL server handles integrity/constraint violations.

You won't be able to drop the PK while any FK still references it. And of course you won't be able to re-create the FK until those other tables also have a column that can fill in for the new column you're adding to the PK.

Related Solutions

SQL Server – Using Table Name Contained in Another Table’s Column

The only way to do this is to query SourceID in to a variable and then query the source table. At that point, you're better off pulling both into variablesm like this:

DECLARE @SourceTable NVARCHAR(255) --Note the length is arbitrary
,@SourceID NVARCHAR(255) --Assuming it's an INT, it doesn't matter
,@sql NVARCHAR(MAX)

SELECT @SourceTable = SourceID
,@SourceID = ReferenceID
FROM MyTable
WHERE SourceID = 'some_table'
AND ReferenceID = 1

SET @SQL = 'SELECT * FROM ' + @SourceTable + ' WHERE InstanceID = ' + @SourceID

EXEC(@sql)

You want the SourceID to be a string because otherwise you'd need to convert it to one anyway.

Sql-server – Horizontal schema vs Vertical schema

Your choice does not have to either/or - you can have both.

At a "physical level" is generally better to use horizontal designs for performance, maintenance and reliability reasons. But the greater rule is: "Don't mix data and metadata". There is nothing intrinsically wrong with building an application that knows how to do CREATE TABLE and ALTER TABLE Foo ADD Col1 by reading metadata table and then allow the app itself to modify the data model. That way, you get the best of both worlds. Think of adding tables and columns the same way you think of DML. Of course, if you do this, you need to make sure you reconfigure the replication when the schema is changed.

Note that the above design guidance doesn't rule out having tables containing the metadata describing what the other tables do (so the app can configure itself to work with it). When building a data warehouse, this is typically an excellent idea. The metadata can even be used to generate and compile new application code.

Best Answer

Related Solutions

SQL Server – Using Table Name Contained in Another Table’s Column

Sql-server – Horizontal schema vs Vertical schema

Related Question