locked
Change/add primary key RRS feed

  • Question

  • User-1826049516 posted

    Hey,

    I need to add another primary key, but I get this error:

    There's a ton of relationships.  One thing I've never done before is programmatically 'export' them to re-add them afterwards.  Is that even possible?

    Thanks

    Saturday, September 3, 2016 4:57 PM

Answers

  • User753101303 posted

    Hi,

    Do you mean that you try to add another column to the existing primary key or that you try to add another primary key (you can have a single pk per table)?

    You don't have to delete all fk constraints but you have to delete those that are using the current primary key. Do a backup first and make sure you changed those tables as well so that you'll be able to recreate fk constraints once deleted (you may have to add columns as well if not done already).

    You can use ALTER TABLE (see https://msdn.microsoft.com/en-us/library/ms190273.aspx) to drop/create FOREIGN KEY constraints.  

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, September 3, 2016 7:03 PM
  • User-2057865890 posted

    Hi Ldoodle,

    One thing I've never done before is programmatically 'export' them to re-add them afterwards.  Is that even possible?

    #Create Foreign Key Relationships

    https://msdn.microsoft.com/en-us/library/ms189049.aspx 

    Create a foreign key in an existing table

    USE AdventureWorks2012; 
    GO 
    ALTER TABLE Sales.TempSalesReason 
    ADD CONSTRAINT FK_TempSales_SalesReason FOREIGN KEY (TempID) 
    REFERENCES Sales.SalesReason (SalesReasonID) 
    ON DELETE CASCADE 
    ON UPDATE CASCADE 
    ; 
    GO

    #Delete Foreign Key Relationships

    https://msdn.microsoft.com/en-us/library/ms189579.aspx 

    To delete a foreign key constraint

    USE AdventureWorks2012; 
    GO 
    ALTER TABLE dbo.DocExe 
    DROP CONSTRAINT FK_Column_B; 
    GO

    Best Regards,

    Chris

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, September 5, 2016 12:12 PM

All replies

  • User753101303 posted

    Hi,

    Do you mean that you try to add another column to the existing primary key or that you try to add another primary key (you can have a single pk per table)?

    You don't have to delete all fk constraints but you have to delete those that are using the current primary key. Do a backup first and make sure you changed those tables as well so that you'll be able to recreate fk constraints once deleted (you may have to add columns as well if not done already).

    You can use ALTER TABLE (see https://msdn.microsoft.com/en-us/library/ms190273.aspx) to drop/create FOREIGN KEY constraints.  

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, September 3, 2016 7:03 PM
  • User-1404113929 posted

    hi,

    make sure one thing  keep scripts for which tables are realted to it ,

    after delete relation ship and update table level primary key 

    and next execute foreign key scripts for re map to primary key table.

    Saturday, September 3, 2016 7:33 PM
  • User-2057865890 posted

    Hi Ldoodle,

    One thing I've never done before is programmatically 'export' them to re-add them afterwards.  Is that even possible?

    #Create Foreign Key Relationships

    https://msdn.microsoft.com/en-us/library/ms189049.aspx 

    Create a foreign key in an existing table

    USE AdventureWorks2012; 
    GO 
    ALTER TABLE Sales.TempSalesReason 
    ADD CONSTRAINT FK_TempSales_SalesReason FOREIGN KEY (TempID) 
    REFERENCES Sales.SalesReason (SalesReasonID) 
    ON DELETE CASCADE 
    ON UPDATE CASCADE 
    ; 
    GO

    #Delete Foreign Key Relationships

    https://msdn.microsoft.com/en-us/library/ms189579.aspx 

    To delete a foreign key constraint

    USE AdventureWorks2012; 
    GO 
    ALTER TABLE dbo.DocExe 
    DROP CONSTRAINT FK_Column_B; 
    GO

    Best Regards,

    Chris

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, September 5, 2016 12:12 PM