query to delete a row with foreign key contraints...

Hello,

I am using sql server 2005 express and have 3 tables

Table1
Dept_Id  (primary key)
Dept_Name


Table2
Employee_Id (primary key)
Dept_Id (foreign key with table1)
Employee_Name


Table3
WorkSchedule_Id (primary key)
Employee_Id (foreign key with table 2)
Date_To_Work (date type)


I want to delete a row from Table1.  I can engineer a simple delete
query.  DELETE * FROM Table1 as D WHERE D.Dept_Name = 'sales'
However, I do not know how to deal with the foreign key constraints.

Any help would be appriciated.
Thanks in advance,
RABMissouri2010


0
RAB
1/22/2010 6:22:21 PM
sqlserver.server 1327 articles. 0 followers. Follow

2 Replies
1044 Views

Similar Articles

[PageSpeed] 56

You have 2 choices:

1)    Delete from the "bottom" up.  IOW, delete rows with FK's pointing to 
the deleted row first, then delete the target row.
2)    Change the constraints to be ON DELETE CASCADE.

That said, are you really sure that you want to delete all of the employees 
in a dept when you delete the dept?

-- 
   Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON   Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau


"RAB" <rabmissouri@yahoo.com> wrote in message 
news:11d011d1-08c3-4026-9cfe-92bf115da312@l30g2000yqb.googlegroups.com...
Hello,

I am using sql server 2005 express and have 3 tables

Table1
Dept_Id  (primary key)
Dept_Name


Table2
Employee_Id (primary key)
Dept_Id (foreign key with table1)
Employee_Name


Table3
WorkSchedule_Id (primary key)
Employee_Id (foreign key with table 2)
Date_To_Work (date type)


I want to delete a row from Table1.  I can engineer a simple delete
query.  DELETE * FROM Table1 as D WHERE D.Dept_Name = 'sales'
However, I do not know how to deal with the foreign key constraints.

Any help would be appriciated.
Thanks in advance,
RABMissouri2010


0
Tom
1/22/2010 6:32:25 PM
> I want to delete a row from Table1.  I can engineer a simple delete
> query.  DELETE * FROM Table1 as D WHERE D.Dept_Name = 'sales'
> However, I do not know how to deal with the foreign key constraints.

To add on to Tom's response, below is example DDL with ON DELETE SET NULL so 
that the employee Dept_Id will be changed to NULL when the associated 
department is deleted (assuming dbo.Table2.Dept_id allows NULL).  Also, rows 
from Table3 will be deleted (ON DELETE CASCADE) when the corresponding 
employee is deleted.

ALTER TABLE dbo.Table2
ADD CONSTRAINT fk_Table2_Table1
FOREIGN KEY(Dept_Id)
REFERENCES dbo.Table1(Dept_Id)
ON DELETE SET NULL;

ALTER TABLE dbo.Table3
ADD CONSTRAINT FK_Table3_Table2
FOREIGN KEY(Employee_Id)
REFERENCES dbo.Table2(Employee_Id)
ON DELETE CASCADE;

-- 
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/


"RAB" <rabmissouri@yahoo.com> wrote in message 
news:11d011d1-08c3-4026-9cfe-92bf115da312@l30g2000yqb.googlegroups.com...
> Hello,
>
> I am using sql server 2005 express and have 3 tables
>
> Table1
> Dept_Id  (primary key)
> Dept_Name
>
>
> Table2
> Employee_Id (primary key)
> Dept_Id (foreign key with table1)
> Employee_Name
>
>
> Table3
> WorkSchedule_Id (primary key)
> Employee_Id (foreign key with table 2)
> Date_To_Work (date type)
>
>
> I want to delete a row from Table1.  I can engineer a simple delete
> query.  DELETE * FROM Table1 as D WHERE D.Dept_Name = 'sales'
> However, I do not know how to deal with the foreign key constraints.
>
> Any help would be appriciated.
> Thanks in advance,
> RABMissouri2010
>
> 
0
Dan
1/23/2010 4:14:01 PM
Reply:

Similar Artilces:

Can dictionary entries be deleted?
I have a large number of German place names that I added to the dictionary and would now like to delete. Is this possible? The custom dictionary is editable from Word's Options > Proofing > Custom Doctionaries (Word 2007) or Tools > Options > Spelling & Grammar > Custom Dictionaries.(Earlier versions) -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ...

I need a product key
I bought a new laptop acer which came with microsoft word and excel installed but each time i try to open it, it always ask for the product key for it to be effective now i cant modify on it. Pls help. Submitted via EggHeadCafe - Software Developer Portal of Choice Silverlight 2 Beta 2 - Doing Data Part IV: Browser / Cookie Interaction http://www.eggheadcafe.com/tutorials/aspnet/9fea2d46-f10a-4152-92e0-4d6f4f5f7063/silverlight-2-beta-2---d.aspx Are you sure it was a Full Version? Most of those come with a Trial Version of Software. If it was supposed to be a Full Version You shou...

Need to search through an entire exch 5.5 database for a key word.
I need to be able to search through an entire exchange 5.5 information store (all mailboxes, all folders, all everything!) for a specific word. Is this possible? If so, how? Do I need a 3rd party tool or is it built in. Thanks! I wrote followin in the other blog: This is a feature with Microsoft Search service and its a part of Windows 2000. Exchange 2000 uses it when you enable "Full Text Indexing". (Its even there in Exchnage 2003) As far as I can remember, it was not a feature in Ex5.5 and yo will need to research on net for some software to enable the test search/indexin...

Case insensitive XPath query?
Hi, I'm using MS SQL Server 2005. I have this clause in a query that looks at an XML column ... (aaa.category_xml.exist('/categories/info_type/category/subcategory/ @name[fn:contains(.,'Mountain Biking')]') = 1) Does anyone know a way to make the "fn:contains" clause do a case insensitive search? Right now the search is based on case but I would like to write one in which case doesn't matter. Thanks for your feedback, - Dave laredotornado (laredotornado@zipmail.com) writes: > I'm using MS SQL Server 2005. I have this clause in...

Looping a selection of rows
Hi I need to create a loop which will select 4 rows copy and paste it to a new sheet. then go to the Next 4 rows and copy them to a new sheet. I need this to happen untill the row is blank (empty). Any idea how to do this loop? I used column A to find that last used row. Option Explicit Sub testme() Dim CurWks As Worksheet Dim NewWks As Worksheet Dim iRow As Long Dim FirstRow As Long Dim LastRow As Long Dim myStep As Long Set CurWks = Worksheets("sheet1") myStep = 4 With CurWks FirstRow = 1 LastRow = .Cel...

change column to row display
I have some data that I paste into a spreadsheet that results in a column of data. Is there a way to paste this column data into a row, or can I convert existing column data in a spreadsheet into a row? Russ Assuming you have less than 256 cells of data (unless you're using XL2007) Try this: Select from the SECOND item down through the bottom of the list. From the Excel Main Menu: <edit><copy> Select the single cell to the right of the FIRST item in the list <edit><paste special>...Check: Transpose....Click [OK] Clear the cells under the first cell. Is that ...

keeping rows together #2
I have a large spreadsheet which consists of a name on the first line and pertinent information regarding that name on the second line. This is repeated for about 500 names. I would like to sort this, but keep the two lines for each person together. OR is there a way to move the info on the 2nd line to the first line (the corresponding cells above the 2nd line are blank)? Obviously, I am looking for a way to do this en masse. Thanks I think you'll have to share the layout... But if your name is in column A of rows 1,3,5,... you could insert a new column A and put this formula...

Form for inputing criteria for a report's query
I have a report that is based on a query. The criteria for the start date changes and I want a form where I can input the date range (Between #2/2/2006# and #2/4/2006#) each time the report is run. Is there a way to set up the form so that the forms asks for the user to put in the start date and the end date and then put those results in the format above into my query to run the report correctly? Thanks, Robert ryonker wrote: >I have a report that is based on a query. The criteria for the start date >changes and I want a form where I can input the date range (Between >#2/2/2...

Storing Query Into Table
I have been trying to find some information about storing query sql strings in a table and calling upon that string to run query's dependant on user selections in a form. I have a split database and I want the users of the front end to create queries that everyone can use. Can anyone point me in the right direction on where to start. Thanks Craig There is a complete query by form applet at http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. This allows users to select field, enter criteria, sort, group, and run. The results are displayed in a datasheet subform...

How can I print noncontiguous rows on the same page?
I want to print rows 3, 9, and 18 on the same page. When I select them with Control and try to print them, I get 3 pages. I also can "Set the Print Area" but it also prints on 3 pages. Other than a workaround to copy and paste to a new sheet, how can I get these 3 rows to print on one page? Thank you. Mary "Mary A Walthall" <Mary A Walthall@discussions.microsoft.com> wrote in message news:DEE7125C-D9A4-4432-AEEE-C3BDA66B4126@microsoft.com... > Other than a workaround to copy and > paste to a new sheet, how can I get these 3 rows to print on one page? ...

How to automatically populate a table with query results
How do I do the following? My database is: Table Title - Main Field in Main Table [Pricingdata] - Numeric - contains Pricing data Query - Contains [Pricingdata] from Main Table Contains Expression Field: ([New Pricing Data]= [Pricingdata] + ..10) Desired Result: I want to automatically overwrite all data in the Main table [Pricingdata] field with results of the query Expression Field. Any assistance is deeply appreciated. On Sat, 24 Mar 2007 09:31:56 -0500, "Frank" <fhsmith7@bellsouth.net> wrote: >I want to automatically overwrite ...

Not confirm delete of message in OL 03
I just had to reinstall OL 03 after a system crash and have forgotten how to have OL delete my email message without asking me to confirm that I really want to delete it. I cannot find the place to uncheck on the Tools | Options menus. Can someone remind me where to find this? Thanks. Bob S <BobS@nospam.com> wrote: > I just had to reinstall OL 03 after a system crash and have forgotten > how to have OL delete my email message without asking me to confirm > that I really want to delete it. I cannot find the place to uncheck > on the Tools | Options menus. Can someo...

Handling very large files (too many rows)
I'm trying to analyse a set of data. However, the data is a CSV fil with almost 1.5 million rows, which somewhat excedes the 65 thousand o so that excel can handle. However, excel is what I'm used to. If ther any convenient way to import so much data? Alternately, can anyon recomend a different means to observe and process that amount of data -- Message posted from http://www.ExcelForum.com I really think that is way too much for Excel, even if you break it into bits. What you should use depends on how you want to analyse the data. Access can handle that quantity of data. Maybe, if...

Change ListView row background color
I have an asp.net listView that I want to change the background color based on a condition. I have started to do something in the ItemDataBound event but cannot find out what method or ??? to use to assign a background color. I have done this in a GridView but this is the first I have tried in ListView. Below is my code if someone can help. Thanks. Protected Sub lvPTODetails_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.ListViewItemEventArgs) Handles lvPTODetails.ItemDataBound Dim dataItem As ListViewDataItem = CType(e.Item, ListView...

Opening file cause rows to lose
I am trying to open a file of 48215 row written in Excel 2003 but only 12121 appeared from 48215 when I am trying to save as Excel 95 format because of another application only reads Excel 95 fromat Thanks It's because Excel 95 does not have that many rows. You need to split them up on multiple sheets -- Regards, Peo Sjoblom "happytoday" <ehabaziz2001@gmail.com> wrote in message news:e211cafe-30fe-45df-af70-864e67f00894@f63g2000hsf.googlegroups.com... >I am trying to open a file of 48215 row written in Excel 2003 but only > 12121 appeared from 48215 when I ...

Display Data In Form From A Query
I have a form with the Control Source as "=Query!QTYForPPMProduction!SumOfQTY_PRODUCED" and this query runs prior to the form opening. The query runs fine, and the proper data displays in the datasheet view, but my form Text Box will not show the data. Any suggestions out there? =DLookup("[SumOfQTY_PRODUCED]", "[QTYForPPMProduction]") -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "GeorgeSoares" <GeorgeSoares@discussions.microsoft.com> wrote in message news:6F2E1E06-8772-4E6F-B1A7-647E14A1A7F0@microsoft.c...

locking rows at top of chart
I am wondering how, in Office XP, to lock the first two rows of my Excel worksheet at the top, like a header, so they stay there when I scroll down the rest of the chart. Blue, Select cell A3. Window, Freeze panes. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "blue-j-rock" <blue-j-rock@discussions.microsoft.com> wrote in message news:FFC5194F-97AE-4550-B32C-23D0A5871D4E@microsoft.com... >I am wondering how, in Office XP, to lock the first two rows of my Excel > worksheet at the top, like a header, so the...

Edit Query
I am new to macros and am trying to write one which enables me to access MSQuery file for editing from my worksheet. The worksheet has to be updated each month so I only need to edit this criteria of the query. Any advice would be greatly appreciated You have 3 different methods you can use 1) Manually create a query using the Excel menus. This type of query is fix (can't change parameters) except using the Query editor from the worksheet menues. Allows only reading of a database or excel file. 2) Record a macro while performing a query (use as a template). Then mo...

Query where 3 Fields must contain same value
I would like to write a query that pulls records where the value in 3 different Columns is the same. Example: Return records where each field below contains same value Return below records Field1 | Field2 | Field3 | 150 150 150 Do not return rows like below Field1 | Field2 | Field3 | 175 26 15 -- Brian Conner Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/200911/1 WHERE Col1 = Col2 AND Col2 = C...

how do i overcome the xl2003 limit of 65,536 rows?
i have a workbook that requires about 9000 rows each month. As I look at the data for a year, i will run out of rows in july or august. the table has 9 columns in it. Is it best to start another worksheet when i run out of rows, or start over in K3 [3 being the 1st row with data]. i generate charts and a pivot table from the data. As everyone at work has xl2003, going to xl 2007 or 2010 is not practicable. any suggestion on how to best accomplish this? Thanks, Tonso hi Tonso, if you have "Access", you can put data into an Access table and analyze it with Excel -- isabelle On...

Hiding rows containing zeroes or blanks in pivot tables?
How do I hide rows containing zeroes or blanks in pivot tables? You can use programming to hide the rows with a zero total. For example: '====================================== Sub HidePivotZeroRows() 'hide worksheet rows that contain all zeros Dim rng As Range For Each rng In ActiveSheet _ .PivotTables(1).DataBodyRange.Rows If Application.Sum(rng) = 0 Then rng.EntireRow.Hidden = True Else 'unhide any previously hidden rows rng.EntireRow.Hidden = False End If Next rng End Sub '================================ Sub UnhidePivotRows() 'unhide all...

delete a link to another work book
I am unable to delete a link from one workbook to another I can see the links under the edit/links command but the only chooses are open source, change source,and update I have searched many help files thaks for any help Hi try the following add-in to find/delete these links: http://www.bmsltd.ie/DLCount/DLCount.asp?file=FindLink.zip -- Regards Frank Kabel Frankfurt, Germany dave wrote: > I am unable to delete a link from one workbook to another > I can see the links under the edit/links command but the > only chooses are open source, change source,and update > > I hav...

i can't seem to delete a document from 2007 word.
i have documents on word that i created during trial period on word. i want to delete them and i can't. isn't there a simple delete method where you highlight the document and it deletes? when i right click i get options about ribbons. when i open saved files, i can't find the documents that are on word 7 "imeighty" <imeighty@discussions.microsoft.com> wrote in message news:CEE4D99F-BFC2-4AE7-B603-A93AD96D5ACD@microsoft.com... > i have documents on word that i created during trial period on word. i > want > to delete them and i can'...

BOM Rolled Cost Report or Query
In GP 7.5 (or later versions?) is there a way to view the rolled up cost of a BOM assembly at current costs of the items? Can I view the assembled cost using the standard cost of the part items as well? To put this anouther way, I want to update the standard cost on the item master record for all items with BOMs with what it would cost today to assemble the BOM. A report(GP or Crystal) or SQL query/view, etc would all work fine. Thanks, Gregg Short MIS Cropking Inc short@cropking.com You could modify Reports-Inventory-Setup-Indented Bill of Materials to calculate a component Extende...

Query works-Export fails "...you and another user are attempting to change the same data..."
Can't figure this out. I am the only user in the database. The file (RealProperty) can be queried using a select query that includes just the fields in the table. I get the above error, however, if I try to export the resulting table/query in any of the following fashions: mdb (in a new db designed to store this one table) ..xls (get the same error) ..csv (get the same error) If it simply try to copy the results, I get the same error. I have checked: there are no dependencies that are missing; there was an export errors table, but the three records that had data issues have been fixed....