SQL Server 2008 - deleting rows with FK constraints

Question

I've got SQL database in SQL Server 2008 generated as follows:

CREATE TABLE Client ( 
    ID bigint,
    Code varchar(50),
    ClientID int NOT NULL
);
ALTER TABLE Client 
  ADD CONSTRAINT PK_Client PRIMARY KEY CLUSTERED (ClientID);

CREATE TABLE Company ( 
    ID bigint,
    Description nvarchar(100),
    SubsidiaryOf bigint,
    companyID int NOT NULL,
    FK_Client_Company int,
    PK_Company int
);
ALTER TABLE Company 
  ADD CONSTRAINT PK_Company PRIMARY KEY CLUSTERED (companyID);

ALTER TABLE Company 
  ADD CONSTRAINT (ID = ID) FOREIGN KEY (FK_Client_Company) 
      REFERENCES Client (ClientID);

ALTER TABLE Company 
  ADD CONSTRAINT (SubsidiaryOf = ID) FOREIGN KEY (PK_Company) 
      REFERENCES Company (companyID);

CREATE TABLE ContactData ( 
    ID bigint,
    LocationID bigint,
    Contact nvarchar(50),
    contactDataID int NOT NULL,
    PK_Location int
);
ALTER TABLE ContactData 
  ADD CONSTRAINT PK_ContactData PRIMARY KEY CLUSTERED (contactDataID);

ALTER TABLE ContactData 
  ADD CONSTRAINT (LocationID = ID) FOREIGN KEY (PK_Location) 
      REFERENCES Location (locationID);


CREATE TABLE Location ( 
    ID bigint,
    CompanyID bigint,
    Country nvarchar(50),
    ZIPCode nvarchar(50),
    locationID int NOT NULL,
    PK_Company int
);
ALTER TABLE Location 
   ADD CONSTRAINT PK_Location PRIMARY KEY CLUSTERED (locationID);

ALTER TABLE Location 
   ADD CONSTRAINT (CompanyID = ID) FOREIGN KEY (PK_Company) 
       REFERENCES Company (companyID);

And would like to delete all the Companies with ID > 140000 (with related rows in other tables). I tried some combination of INNER JOINs all together in one transaction, but there is still a problem with FK_Client_Company constraint. Can anyone help me?

One more thing - I cannot add anything/modify DB structure/constraints. It has to be a query-base-solution.

See: http://stackoverflow.com/questions/6260688/sql-server-cascade-delete — Eddy, Nov 14 '11 at 20:46

Brandon Moore · Accepted Answer · 2011-11-15T08:24:37.030

0

First delete those companies' clients

    delete client where id in (select fk_client_company from company where id > 140000)

After that you should be able to run the delete statement on the company table

    delete company where id > 140000

I'm 'fairly' sure that's the answer you're looking for but I'm not a 100% positive only because your naming scheme seems a little odd. I'm making the assumption that company.fk_client_company = client.id.

edited Nov 15 '11 at 08:24

answered Nov 15 '11 at 00:23

Brandon Moore

8,590
15
65
120

You say it has to be a 'query based' solution. I am assuming you realize you can modify the database schema with queries, and that you are just saying you don't have access to do that. If you do in fact have access to do that, then I would recommend adding an 'Active' field to the company table and just mark those companies as inactive. That may not be a possible solution since maybe you're working with applications you can't change to filter on a new 'Active' field, but I just wanted to throw that out there. – Brandon Moore Nov 15 '11 at 00:27
Thanks a lot! I've put all this code in BEGIN TRAN and COMMIT TRAN – J.R. Nov 15 '11 at 08:19
Oh, I just realized I forgot something. "(select fk_client_company where id > 140000)" should be "(select fk_client_company from company where id > 140000)". I will fix it in the answer. – Brandon Moore Nov 15 '11 at 08:24

SQL Server 2008 - deleting rows with FK constraints

1 Answers1