locked
Unable to delete row RRS feed

  • Question

  • Hello Community,

    Each time I attempt to delete a row from my table I get the following error:

    My table is as follows:

    CREATE TABLE AdventureWorks2012.Person.Address (
      AddressID INT IDENTITY NOT FOR REPLICATION
     ,AddressLine1 NVARCHAR(60) NOT NULL
     ,AddressLine2 NVARCHAR(60) NULL
     ,City NVARCHAR(30) NOT NULL
     ,StateProvinceID INT NOT NULL
     ,PostalCode NVARCHAR(15) NOT NULL
     ,SpatialLocation GEOGRAPHY NULL
     ,rowguid UNIQUEIDENTIFIER NOT NULL CONSTRAINT DF_Address_rowguid DEFAULT (NEWID()) ROWGUIDCOL
     ,ModifiedDate DATETIME NOT NULL CONSTRAINT DF_Address_ModifiedDate DEFAULT (GETDATE())
     ,CONSTRAINT PK_Address_AddressID PRIMARY KEY CLUSTERED (AddressID)
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO
    
    CREATE UNIQUE INDEX AK_Address_rowguid
    ON AdventureWorks2012.Person.Address (rowguid)
    ON [PRIMARY]
    GO
    
    CREATE UNIQUE INDEX IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode
    ON AdventureWorks2012.Person.Address (AddressLine1, AddressLine2, City, StateProvinceID, PostalCode)
    ON [PRIMARY]
    GO
    
    CREATE INDEX IX_Address_StateProvinceID
    ON AdventureWorks2012.Person.Address (StateProvinceID)
    ON [PRIMARY]
    GO
    
    ALTER TABLE Person.Address
    ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON)
    GO
    
    ALTER TABLE AdventureWorks2012.Person.Address
    ADD CONSTRAINT FK_Address_StateProvince_StateProvinceID FOREIGN KEY (StateProvinceID) REFERENCES Person.StateProvince (StateProvinceID)
    GO
    
    EXEC sys.sp_addextendedproperty N'MS_Description'
                                   ,N'Foreign key constraint referencing StateProvince.StateProvinceID.'
                                   ,'SCHEMA'
                                   ,N'Person'
                                   ,'TABLE'
                                   ,N'Address'
                                   ,'CONSTRAINT'
                                   ,N'FK_Address_StateProvince_StateProvinceID'
    GO
    
    EXEC sys.sp_addextendedproperty N'MS_Description'
                                   ,N'Street address information for customers, employees, and vendors.'
                                   ,'SCHEMA'
                                   ,N'Person'
                                   ,'TABLE'
                                   ,N'Address'
    GO
    
    EXEC sys.sp_addextendedproperty N'MS_Description'
                                   ,N'Primary key for Address records.'
                                   ,'SCHEMA'
                                   ,N'Person'
                                   ,'TABLE'
                                   ,N'Address'
                                   ,'COLUMN'
                                   ,N'AddressID'
    GO
    
    EXEC sys.sp_addextendedproperty N'MS_Description'
                                   ,N'First street address line.'
                                   ,'SCHEMA'
                                   ,N'Person'
                                   ,'TABLE'
                                   ,N'Address'
                                   ,'COLUMN'
                                   ,N'AddressLine1'
    GO
    
    EXEC sys.sp_addextendedproperty N'MS_Description'
                                   ,N'Second street address line.'
                                   ,'SCHEMA'
                                   ,N'Person'
                                   ,'TABLE'
                                   ,N'Address'
                                   ,'COLUMN'
                                   ,N'AddressLine2'
    GO
    
    EXEC sys.sp_addextendedproperty N'MS_Description'
                                   ,N'Name of the city.'
                                   ,'SCHEMA'
                                   ,N'Person'
                                   ,'TABLE'
                                   ,N'Address'
                                   ,'COLUMN'
                                   ,N'City'
    GO
    
    EXEC sys.sp_addextendedproperty N'MS_Description'
                                   ,N'Unique identification number for the state or province. Foreign key to StateProvince table.'
                                   ,'SCHEMA'
                                   ,N'Person'
                                   ,'TABLE'
                                   ,N'Address'
                                   ,'COLUMN'
                                   ,N'StateProvinceID'
    GO
    
    EXEC sys.sp_addextendedproperty N'MS_Description'
                                   ,N'Postal code for the street address.'
                                   ,'SCHEMA'
                                   ,N'Person'
                                   ,'TABLE'
                                   ,N'Address'
                                   ,'COLUMN'
                                   ,N'PostalCode'
    GO
    
    EXEC sys.sp_addextendedproperty N'MS_Description'
                                   ,N'Latitude and longitude of this address.'
                                   ,'SCHEMA'
                                   ,N'Person'
                                   ,'TABLE'
                                   ,N'Address'
                                   ,'COLUMN'
                                   ,N'SpatialLocation'
    GO
    
    EXEC sys.sp_addextendedproperty N'MS_Description'
                                   ,N'ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.'
                                   ,'SCHEMA'
                                   ,N'Person'
                                   ,'TABLE'
                                   ,N'Address'
                                   ,'COLUMN'
                                   ,N'rowguid'
    GO
    
    EXEC sys.sp_addextendedproperty N'MS_Description'
                                   ,N'Date and time the record was last updated.'
                                   ,'SCHEMA'
                                   ,N'Person'
                                   ,'TABLE'
                                   ,N'Address'
                                   ,'COLUMN'
                                   ,N'ModifiedDate'
    GO
    
    EXEC sys.sp_addextendedproperty N'MS_Description'
                                   ,N'Unique nonclustered index. Used to support replication samples.'
                                   ,'SCHEMA'
                                   ,N'Person'
                                   ,'TABLE'
                                   ,N'Address'
                                   ,'INDEX'
                                   ,N'AK_Address_rowguid'
    GO
    
    EXEC sys.sp_addextendedproperty N'MS_Description'
                                   ,N'Nonclustered index.'
                                   ,'SCHEMA'
                                   ,N'Person'
                                   ,'TABLE'
                                   ,N'Address'
                                   ,'INDEX'
                                   ,N'IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode'
    GO
    
    EXEC sys.sp_addextendedproperty N'MS_Description'
                                   ,N'Nonclustered index.'
                                   ,'SCHEMA'
                                   ,N'Person'
                                   ,'TABLE'
                                   ,N'Address'
                                   ,'INDEX'
                                   ,N'IX_Address_StateProvinceID'
    GO
    
    EXEC sys.sp_addextendedproperty N'MS_Description'
                                   ,N'Clustered index created by a primary key constraint.'
                                   ,'SCHEMA'
                                   ,N'Person'
                                   ,'TABLE'
                                   ,N'Address'
                                   ,'INDEX'
                                   ,N'PK_Address_AddressID'
    GO
    
    EXEC sys.sp_addextendedproperty N'MS_Description'
                                   ,N'Primary key (clustered) constraint'
                                   ,'SCHEMA'
                                   ,N'Person'
                                   ,'TABLE'
                                   ,N'Address'
                                   ,'CONSTRAINT'
                                   ,N'PK_Address_AddressID'
    GO
    
    EXEC sys.sp_addextendedproperty N'MS_Description'
                                   ,N'Default constraint value of GETDATE()'
                                   ,'SCHEMA'
                                   ,N'Person'
                                   ,'TABLE'
                                   ,N'Address'
                                   ,'CONSTRAINT'
                                   ,N'DF_Address_ModifiedDate'
    GO
    
    EXEC sys.sp_addextendedproperty N'MS_Description'
                                   ,N'Default constraint value of NEWID()'
                                   ,'SCHEMA'
                                   ,N'Person'
                                   ,'TABLE'
                                   ,N'Address'
                                   ,'CONSTRAINT'
                                   ,N'DF_Address_rowguid'
    GO

    Can someone let me know what I need to do to delete the row?

    Thanks


    carlton7372

    Thursday, October 10, 2019 2:42 PM

Answers

  • Your AddressID column is referenced by a Foreign key in another table:Person.BusinessEntityAddress .

    You need to delete the related rows first then do the delete from your current table. Or you can set the foreign key relation setting for cascading delete to make your delete work.

    • Proposed as answer by Tom Phillips Thursday, October 10, 2019 6:49 PM
    • Marked as answer by carlton7372 Thursday, October 10, 2019 6:56 PM
    Thursday, October 10, 2019 3:00 PM

All replies

  • Your AddressID column is referenced by a Foreign key in another table:Person.BusinessEntityAddress .

    You need to delete the related rows first then do the delete from your current table. Or you can set the foreign key relation setting for cascading delete to make your delete work.

    • Proposed as answer by Tom Phillips Thursday, October 10, 2019 6:49 PM
    • Marked as answer by carlton7372 Thursday, October 10, 2019 6:56 PM
    Thursday, October 10, 2019 3:00 PM
  • I tried your suggestion of deleting the related rows, but I can't see the related rows you suggested?

    Any further assistance please


    carlton7372

    Friday, October 11, 2019 10:01 AM
  • We cannot see your other table either.

    Do you have any errors for your delete? Follow the error message.

    Friday, October 11, 2019 1:22 PM