Cascade Deletions Of Children When FKs Present
Dec 8, 1999
Guys, could anyone tell me if MSSQL Server 7 has 'on delete cascade' option when creating a foreign key constraint or something similar to it. I'd really like MSSQL to remove all dependent records (child records) automatically from one table when I'm deleting a parent record from another record. I know that I can do it via trigger, but the FK constraint should be removed or disabled. I would really appreciate your help. Thank you very much.
View 1 Replies
ADVERTISEMENT
Nov 21, 2012
I have the below query which in turn goes in the where clause
Code:
SELECT CASE WHEN EXISTS (SELECT child.id FROM InspectionType parent,
InspectionType child WHERE parent.id = child.parentId AND
parent.code = 'INSPECTION_TYPE_SAFETY_LIFE' AND parent.zoneId = 10129)
THEN (SELECT child.id FROM InspectionType parent, InspectionType child
WHERE parent.id = child.parentId AND parent.code ='INSPECTION_TYPE_SAFETY_LIFE' AND parent.zoneId = 10129)
ELSE (SELECT id FROM InspectionType WHERE code = 'INSPECTION_TYPE_SAFETY_LIFE' AND zoneId = 10129)
END
I am getting below error
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I tried to solve this problem with If and else but it is not going well with in-clause
Code:
select * from InspectionType where id in (select IF EXISTS (SELECT child.id FROM InspectionType parent,
InspectionType child WHERE parent.id = child.parentId AND
parent.code = 'INSPECTION_TYPE_SAFETY_LIFE' AND parent.zoneId = 10129)
BEGIN (SELECT child.id FROM InspectionType parent, InspectionType child
WHERE parent.id = child.parentId AND parent.code ='INSPECTION_TYPE_SAFETY_LIFE' AND parent.zoneId = 10129)
END
ELSE (SELECT id FROM InspectionType WHERE code = 'INSPECTION_TYPE_SAFETY_LIFE' AND zoneId = 10129) )
I am using select * from InspectionType where id in() for illustation only. The sub-query results will go another query
Code:
SELECT child.id FROM InspectionType parent, InspectionType child
WHERE parent.id = child.parentId AND parent.code ='INSPECTION_TYPE_SAFETY_LIFE' AND parent.zoneId = 10129
This returns more than one row.I know why is the error message but need to achieve this functionality.
View 1 Replies
View Related
Aug 18, 2015
I have made an SSRS report using the recursive parent functionality to show a hierarchical tree of values. The problem I have is that some children have more than one parent, but because (in order to use the recursive parent nicely) I need to group the results by Id, I only see distinct entries. This means that I only see each child once, even if it "should" appear in multiple locations in the report (under each of its parents).
View 6 Replies
View Related
Jul 23, 2005
Quick question about deleting data from SQL Server.We have a table that gets quite a bit of activity with an attribute oftype text (inserts that store new text entries of 50-200k apiece).Older rows aren't needed so we have a process that deletes rows morethan 30 days old (using delete statements).When these rows are deleted, is the memory consumed by theseautomatically recovered? Or is there some process that must beperformed to recover the space? What about the transaction log? Doesthat grow with each deletion? When do transaction logs get reset?Thanks,John
View 2 Replies
View Related
Jun 25, 2002
Does an Differential Backup contain all the changes since the last full backup, including Inserts, Updates, and Deletions. Our DB has "Truncate on Checkpoint" = True, so Log backups are non-sensical. I want to apply the Differentials to an archive, offline DB.
"one pebble does not fill the void, but it is a start."
"Dedicated to only creating original mistakes."
View 1 Replies
View Related
Jul 20, 2005
hello to all,there are two databases named A and B.One database contains employee id as primaryI have another project database which includes that employee id asforeign key. How can I reflect my updations,deletionsthank you in advance,vishnu
View 2 Replies
View Related
Jul 23, 2005
I have written a trigger that's supposed to go out and deletecorresponding records from multiple tables once I delete a specificrecord from a table called tblAdmissions.This does not work and I'm not sure why...Here's the code that's supposed to run, let's say, if a user (via a VB6.0 interface) decides to delete a record. If the record in thetblAdmissions table has the primary key (AdmissionID) of "123", thenthe code below is supposed to search other tables that have relatedinformation in them and also have an AdmissionID of "123" and deletethat information as well.Any ideas? Here's the code:CREATE TRIGGER tr_DeleteAdmissionRelatedInfo-- and here is the table nameON tblAdmissions-- the operation type goes hereFOR DELETEAS-- I just need one variable this timeDECLARE @AdmissionID int-- Now I'll make use of the deleted virtual tableSELECT @AdmissionID = (SELECT @AdmissionID FROM Deleted)-- And now I'll use that value to delete the data in-- the tblASIFollowUp TableDELETE FROM tblASIFollowUpWHERE AdmissionID = @AdmissionID-- And now I'll use that value to delete the data in-- the tblProgramDischarge TableDELETE FROM tblProgramDischargeWHERE AdmissionID = @AdmissionID-- And now I'll use that value to delete the data in-- the tblRoomAssignment TableDELETE FROM tblRoomAssignmentWHERE AdmissionID = @AdmissionID-- And now I'll use that value to delete the data in-- the tblTOADS TableDELETE FROM tblTOADSWHERE AdmissionID = @AdmissionID-- And now I'll use that value to delete the data in-- the tblUnitedWaySurvey TableDELETE FROM tblUnitedWaySurveyWHERE AdmissionID = @AdmissionID-- And now I'll use that value to delete the data in-- the tblWFGMSurvey TableDELETE FROM tblWFGMSurveyWHERE AdmissionID = @AdmissionID
View 4 Replies
View Related
Sep 1, 2005
I wanted to know if SQL Server 2000 does something behind the scenes in a transparent manner whenever records are inserted/deleted from two tables between which a join is defined based on a primary key to foreign key relationship. So I have already defined a parent-child relationship through the 'Database Diagram' between these 2 tables. I know when a table is indexed then SQL Server will perform some actions behind the scenes in a transparent manner.The reason I am asking this question is to know if its bad to define parent-child relatioship between 2 tables that will each contain thousands or millions of records.
View 2 Replies
View Related
Sep 10, 2007
hi. I am working on Multiline Marketing Project.I have to calculate all the childens and display the whole tree..I have save records in tree format...But not able to calculate the all childrens.
SELECT count(*) FROM dfTree WHERE id in (SELECT id FROM dfTree WHERE lineage like '16%')
This query works properly.Problem is that it is not working in the project because in the like I have to pass a variable.
SELECT count(*) FROM dfTree WHERE id in (SELECT id FROM dfTree WHERE lineage like '@idl%')
Please Suggest me...
View 1 Replies
View Related
Dec 30, 2004
Hi all, Im designing a product menu like the one seen here.
I need to be able to itterate through all product menu groups in a table and find all children of a specified parent. Sorry for the cryptic Explanation and crappy Diagram!
Please see my diagram
View 5 Replies
View Related
Nov 17, 2005
Hi,
I need to be able to identify if a parent has the same child names as another parent.
Have a look at my data (in pic)
Can you think of a SELECT that will count for each parent, the number of other parents including themselves with all the same children's names?
So you would get something like:
Bob | 2
Bill | 1
Glen | 2
thanks,
david.
View 7 Replies
View Related
Jul 12, 2007
Hi
I am creating a SSRS report connecting to a cube , Which has geography as Dimension as Hirerarchy ( the Hirerachy consists of Region, Subregion) and a measure , Now this geography dimension is taken as Parameter
when i apply filters i am getting the filter based on Parameter selection , But how can i get the all children under the same parent
As i need to create a graph based on report parameter (ie i should get all the children under the Select parameter parent)
to be more specfic consider this
Region SubRegion
Asia India
Asia Pakistan
Asia Japan
Australia Melborne
Suppose my report parameter is India , I should get all the values under Asia(Parent of india)
Can any one help me ..
View 2 Replies
View Related
Jan 5, 2013
Finding the court cases where all children associated with that case have a programClosureDate. I can run this query:
CaseInfo Table
CaseID,
CaseNumber,
CaseName
CaseChild Table
CaseID, FK to CaseInfo
ChildPartyID, FK to PartyID in Party table
ProgramClosureDate
Party Table
ID,
PartyID,
Firstname,
LastName
SELECT ci.CaseNumber, ci.CaseName, p.firstname+' '+p.lastname AS child, cc.programClosureDate
FROM CaseInfo ci JOIN
CaseChild cc ON ci.CaseID = cc.CaseID JOIN
Party p ON cc.ChildPartyID = p.PartyID
WHERE cc.ProgramClosureDate IS NOT NULL
ORDER BY ci.CaseName
But this does not give me the cases where all the children have programCLosureDate IS NOT NULL.
View 5 Replies
View Related
Sep 20, 2006
I am working on a project where I have a page that will have a parent record (Product) and then 1 or more children (options available for the product, user enters text to define) displayed in a table/gridview. There is a relationship defined in the database between the product and options table). My question is how can I allow the user to add the product info and then within the same page also add the options and only then save it all? The options will added to a table. Thanks for any help
View 2 Replies
View Related
Apr 11, 2007
I need to extract data to send to an external agency in their supplied format. The data is normalised in our system in a one to many relationship. The external agency needs it denormalised.
In our system, the parent p has p_id, p_attribute_1, p_attribute_2, p_attribute_3 and the child has c_id, c_attribute_a, c_attribute_b, c_parent_id_fk
The external agency can only use a delimited file looking like
p_id, p_attribute_1, p_attribute_2, p_attribute_3, c1_attribute_a, c1_attribute_b, c2_attribute_a, c2_attribute_b, ...., cn_attribute_a, cn_attribute_b
where n is the number of children a parent may have. Each parent can have 0 or more children - typically between 1 and 20.
How can I achieve this using SSIS? In the past I have used custom built VB apps with the ADO SHAPE command but this is not ideal as I have to rebuild each time to alter the selection criteria and and VB is not a good SQL tool.
View 4 Replies
View Related
Apr 8, 2015
Include children and exclude children in a single hierarchy in parent child dimension in mdx
*12-parent
**20-parent
- 9-parent
--250-child1
--210-child2
--240-child3
aggregation of 12-parent only
aggregation of 20-parent only
aggregation of 9 with children
View 3 Replies
View Related
Apr 16, 2008
Hi,
I've been asked to find a way to insert a row only if it's not present. I want to make sure that test and insert are atomic, so I wrote the following test cases
-- CREATE TABLE t1 ( id int, val varchar(256) )
-- GO
-- Execute the following statements in one query window
IF NOT EXISTS(SELECT * FROM t3 WHERE id = 1)
BEGIN
WAITFOR DELAY '00:00:10'
INSERT INTO t3(id, val) VALUES (1, 'Query 1')
END
GO
-- Then execute the following statements in a second query window within ten
-- seconds of the starting the first query
IF NOT EXISTS(SELECT * FROM t3 WHERE id = 1)
BEGIN
WAITFOR DELAY '00:00:10'
INSERT INTO t3(id, val) VALUES (1, 'Query 2')
END
GO
When both queries are complete there will be two rows in the table t with id = 1.
1 Query 2
1 Query 1
Wrapping the code in a transaction does not change the result. Changing the transaction isolation level to serialable causes a deadlock, but otherwise the isolation level has no effect on the result either. Adding a WITH (TABLOCK) hint to the tests do not change the result.
Our clients use a mix of SQL 2000 and SQL 2005 so TRY/CATCH blocks cannot be used.
Any suggestion on how to do this?
Thanks
View 11 Replies
View Related
Feb 4, 2008
I'm developing a report which contains a number of matrixes which are ordered in a hierarchy which can be navigated using a document map. One of the matrixes contains a subreport containing a table which displays a description for the matrix and the corresponding document map label. This is working without problem already in the production environment.
Recently, after updating my machine with numerous updates and patches, this stopped functioning. I now receive the error message, "The given key was not present in the dictionary" when I run this subreport, either as part of the greater report or on its own.
I have seen the posting which recommends deleting the report.rdl.data files. This did not solve the issue.
The data and the dataset are both in sync. When I click the "Print layout" button when previewing the report in Visual Studio .Net 2005, I can view the data. This looks like a bug in the VS.NET 2005 report designer.
I am running SQL Server 2005 SP2.
Any help is appreciated.
Regards,
Bill Mueller
View 3 Replies
View Related
Dec 11, 2006
Hi all,
Publisher
SQL 2000 Snapshot publication timed to run at 23:00 daily All tables & views are published articles.Snapshot agent runs and creates a snapshot of all articles.
Subscriber
SQL 2005 Pull subscription set to run continuously Manually run the SQL agent job and I get the following error... 2006-12-11 15:04:35.687 Agent message code 20164. Unable to replicate a view or function because the referenced objects or columns are not present on the Subscriber.
I've tried creating the subscription database from a backup so I know all the tables & views exist and also tried just creating a blank empty database - same error.
There are no user stored procedures or functions in the database.
If I'm publishing all tables & views then why should the data & structure fail to load at the subscriber? Do I need two publications, the first containing the metadata and the second containing the data?
Thanks in advance
Dave
View 6 Replies
View Related
Aug 30, 2015
I am using the code below to get all the children of a particular product and it is working fine. How to get the particular product's id in the select statement. for example, i need to show 891 in a separate column for all the records returned by the query below.
DECLARE @Hierarchy TABLE (Product_Id INT, Parent_Product_Id INT)
INSERT INTO @Hierarchy VALUES (123, 234)
INSERT INTO @Hierarchy VALUES (234, 456)
INSERT INTO @Hierarchy VALUES (456, 678)
INSERT INTO @Hierarchy VALUES (678, 891)
INSERT INTO @Hierarchy VALUES (891, NULL)
[Code] .....
View 3 Replies
View Related
Mar 6, 2000
Hi! I'm new to SQL server. I need help.
I have 2 tables:
Table A (A_ID, X) where A_ID is the primary key.
Table B (B_ID, A_ID, Y) where B_ID is the primary key, and A_ID is the foreign key.
I want to change the value of A_ID of Table A, say A1 into A2. How can I automatically change the value of A_ID in Table B if the record exists?
How can I delete a record in Table A and the corresponding record in Table B is deleted automatically?
Thanks in advance!
Chung
View 1 Replies
View Related
Jul 8, 2006
Does anyone know how to do a cascade update in SQL 2005 using the studio manager?Basically, I have a project table with a status column. If the status is set to 2, then I need to update another table that references the project id.
View 1 Replies
View Related
Feb 14, 2007
I have a Sql Server 2005 table with 3 fields, an ID field (primary key), a parent ID field, and Name. The parent ID references the ID field (foreign to primary - many to one) within the same table so that records can reference their parent. I would like to place a cascade delete on the ID field so that when the primary ID is removed it will automatically remove all those records with a parent ID that match. Sql server does not allow me to establish this cascade delete.I was considering a trigger instead but only know how tio use the AFTER paramter and not an alternative. Thanks
View 2 Replies
View Related
Jun 11, 2002
Hi,
We are developing a new oltp application using SQL Server 2000 and are debating whether to use "cascade delets" or not. In earlier apps, we had issues using cascade deletes like someone deleted parent by mistake and all child records got deleted OR SQL Server crashed in middle of cascade delete and records were lost or performance became an issue when there were huge # of child records to be deleted,etc.
Are there any recommendations for/against using Cascade deletes and when to use and when NOT to use cascade deletes ?
Thanks
Satish
View 2 Replies
View Related
Aug 27, 2006
Hello all,
I am new to SQL and I was hoping someone could explain something to me about 'on update cascade'.
I understand what 'on update cascade' does (i think) - it updates the child table when the parent table is updated.
What i do not understand is that the 'on update cascade' works on the primary key of the parent table, but i was on the understanding that the primary key doesn't change often if at all so why would the 'on update cascade' be of use?
Sorry for my ignorance and i realise i must be missing something simple but would be grateful for any help.
Thanks
View 5 Replies
View Related
Nov 6, 2006
OK, so now I'm just being lazy, but after seeing a question about this, I wonder which is actually better?
Assuming that you have SPROC only access to your data, which is better/more effeicient.
Having the stored procedure actually do the deletes/updates (which is the way I always do this) or enabling CASCADE DELETE/UPADTE in the database?
Just curious, what do most people do.
Logically to me, the "UPDATE" is actually a logical DELETE and INSERT anyway because it's a new "thing"
Unless of course you buy into surrogates
Brett
8-)
Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Add yourself!
http://www.frappr.com/sqlteam
View 12 Replies
View Related
Apr 23, 2007
i just got a job and i have to learn triggers to pass my test , however,i'mfinding the cascade trigger a bit difficult, does it update other tables automatically when a column is updated?
View 2 Replies
View Related
Nov 13, 2007
I am a C# programmer also acting as the dba for a SQL Server project. I have a table called Folder that has child records in a table called FolderItems. When a Folder is deleted I want the FolderItems to be deleted. I know how to accomplish via code, but I would prefer to have SQL Server perform the delete just in case a folder is deleted via SQL Management Studio or other method.
Is a trigger the way to go about this? I know how to create triggers For Delete, but I just wondering is this the best method to create a Cascade delete or does SQL have any built in "Cascade" delete features.
I am using SQL Server 2005 if this matters.
Thank you,
Corby Nichols
Flying Elephant Software
View 7 Replies
View Related
Mar 7, 2008
Helle people. The question is:
I have two tables: Directories and SubDirectories. SubDirectories table has foreign key for DirectoryId. Now if I define this key with ON DELETE CASCADE attribute then in case of I delete a Directory record, all SubDirectory records will also be deleted. But if I have only one table Directories that have field named ParentId and in this field I save an Id of parent direcory. Can I define a parentId field as a foreign key to the same table and define it with ON DELETE CASCADE? Will it work too? Thanks
View 1 Replies
View Related
Mar 12, 2008
Hi,
I want to know more about delete cascade feature of SQL Server 2005.
View 6 Replies
View Related
Jun 16, 2008
Hello. I have been challanged to build an application that keeps track of inventory and dates of every item that needs inspection on regular intervals. The original application was a .NET windows application and i have been tasked to "port" and improve it to a .NET C# web application.
The original database was in access and i have imported everyting to SQL and all the data is in a single table. So far the table is relatively small so i decided to keep the current database design.
What i need to do is to select all the items that are comming up for inspection in the comming months and present them. Each item has a uniqe article number (artnr) with the item name, category and location of the item. The location is the part i would like each items to be presented by. Basicly i would like to retrive artnr, name, type and present x number of gridviews or a sorted presentation where each item is shown inside each gw/whatever by location.
I also considered moving all locations to it's own table and connect it with a PK/FK to the prinmary article table but i dont know if this will help me or not. Basicly i have no idea how i would present the items by location where each gridview/somethign else is it's own location.
I could really use som help with how i can retrive and present the data so it is showed in the above manner.
View 1 Replies
View Related
Feb 16, 2007
Hi,
We need to create a tree stucture output of a query.
In case of Oracle , there is 'CONNECT BY' Clause for creating the same.
Does SQL server support anything of this sort?
If yes, can anybody please provide the syntax for the same?
Regards
Kiran
View 4 Replies
View Related
Dec 3, 2007
Dear All,
I want to retrive the records if are created in a particular week.
if i pass 03/12/2007 , i want get records which are created in that week
View 11 Replies
View Related
