delete contraint

HI all,
Hope I have the correct terminology.
I have related tables, with cascading deletes off. In otherwords, you cannot
delete a parent row if there are related child row.
Now when this happens a correct error is produced.
Is there any way to have a single return statement stipulation which child
table was involved in the cascading delete( or rarther the parent delete
failing)
Reasoning behind this is that because I have many child tables related with
a single parent, it would be a lot easier to know which table cause the
delete to fail, go to that table and determin if row in that particular
table can be deleted.
Currently I have to go to every related child table, and figure out if it
caused the delete to fail, and if ti did, then take the needed action.
I'm really looking for principals here, or for someone to steer me in the
right direction, perhaps some reading material etc.
Thanks
RobertAre you saying that you want to know which referencing table is it that proh
ibits the DELETE
operation? Check out the error message from the DELETE operation. If you hav
e several referencing
tables that has a conflict, you will only get on of them, though:
CREATE TABLE rd(c1 int primary key)
INSERT INTO rd (c1) VALUES(1)
GO
CREATE TABLE rs1(c1 int REFERENCES rd(c1))
CREATE TABLE rs2(c1 int REFERENCES rd(c1))
INSERT INTO rs2 (c1) VALUES(1)
CREATE TABLE rs3(c1 int REFERENCES rd(c1))
INSERT INTO rs3 (c1) VALUES(1)
GO
DELETE FROM rd WHERE c1 = 1
Server: Msg 547, Level 16, State 1, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK__rs2__c1__
4D0CD9BB". The conflict
occurred in database "tempdb", table "dbo.rs2", column 'c1'.
The statement has been terminated.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Robert Bravery" <me@.u.com> wrote in message news:egQxAm8MGHA.2828@.TK2MSFTNGP12.phx.gbl...[
color=darkred]
> HI all,
> Hope I have the correct terminology.
> I have related tables, with cascading deletes off. In otherwords, you cann
ot
> delete a parent row if there are related child row.
> Now when this happens a correct error is produced.
> Is there any way to have a single return statement stipulation which child
> table was involved in the cascading delete( or rarther the parent delete
> failing)
> Reasoning behind this is that because I have many child tables related wit
h
> a single parent, it would be a lot easier to know which table cause the
> delete to fail, go to that table and determin if row in that particular
> table can be deleted.
> Currently I have to go to every related child table, and figure out if it
> caused the delete to fail, and if ti did, then take the needed action.
> I'm really looking for principals here, or for someone to steer me in the
> right direction, perhaps some reading material etc.
> Thanks
> Robert
>[/color]

Delete and update

Hi All,

I have a Access front-end,the tables are from SQL server 2000 and
linked via ODBC using DSN.
I have a Main table and several related tables.The Main table has a
One -to- Many relationship with the related tables.
When I try to delete a record from the front-end,I get a error msg "
the table is locked,you and another user are attempting to delete/
update the same record".I am the single user and I am at a loss to
understand what the problem is.

I have to explain that I never had a problem when i was using Access
as the backend too.Is it because of linking?

Any suggestions?

RoyAre you are running the sql statement from Access?
Is the DSN a User DSN or System DSN?

--

Jack Vamvas
___________________________________
The latest IT jobs - www.ITjobfeed.com
<a href="http://links.10026.com/?link=http://www.itjobfeed.com">UK IT Jobs</a>

"Roy" <praish1998@.yahoo.comwrote in message
news:1173148692.840874.72780@.c51g2000cwc.googlegro ups.com...

Quote:

Originally Posted by

Hi All,
>
I have a Access front-end,the tables are from SQL server 2000 and
linked via ODBC using DSN.
I have a Main table and several related tables.The Main table has a
One -to- Many relationship with the related tables.
When I try to delete a record from the front-end,I get a error msg "
the table is locked,you and another user are attempting to delete/
update the same record".I am the single user and I am at a loss to
understand what the problem is.
>
I have to explain that I never had a problem when i was using Access
as the backend too.Is it because of linking?
>
Any suggestions?
>
Roy
>

|||Jack Vamvas wrote:

Quote:

Originally Posted by

Are you are running the sql statement from Access?
Is the DSN a User DSN or System DSN?

What SQL statements does Profiler show as being attempted?
How are the relationships enforced (which ON DELETE option is in use)?

Delete all records in Table 1 where related record has value in Ta

I believe this should achieve what you want to do, just replace 5 with
whatever value you want:
DELETE a FROM table1 a JOIN table2 b ON (a.ID_Table2 = b.id) AND (b.value =
5)
Adam J Warne, MCDBA
"Imager" wrote:

> Simple query for you query gurus here:
> Given two tables, related by ID_Adjacency, how can I delete all rows in
> table 1, where the related row in table 2 has some given value? ie.: with
> the following two tables, delete all records from Table1, where the relate
d
> record in Table 2 has a "Value" field with a value of 2.
>
> CREATE TABLE [dbo].[Table2] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [Value] [tinyint] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Table1] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [ID_Table2] [int] NOT NULL ,
> [Value] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Table2] WITH NOCHECK ADD
> CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
> (
> [ID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Table1] WITH NOCHECK ADD
> CONSTRAINT [PK_SourceTable] PRIMARY KEY CLUSTERED
> (
> [ID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Table1] ADD
> CONSTRAINT [FK_Table1_Table2] FOREIGN KEY
> (
> [ID_Table2]
> ) REFERENCES [dbo].[Table2] (
> [ID]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> GO
>
>On Tue, 16 Aug 2005 03:39:02 -0700, Adam Warne wrote:
>I believe this should achieve what you want to do, just replace 5 with
>whatever value you want:
>DELETE a FROM table1 a JOIN table2 b ON (a.ID_Table2 = b.id) AND (b.value = 5)[/col
or]
Or the more protable ANSI-stnadard version:
DELETE FROM table1
WHERE EXISTS (SELECT *
FROM table2
WHERE table2.ID = table1.ID_Table2
AND table2.value = 5)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks chaps.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:5mf4g19hbk4rtf9khjg7t428kllhgeudsc@.
4ax.com...
> On Tue, 16 Aug 2005 03:39:02 -0700, Adam Warne wrote:
>
> Or the more protable ANSI-stnadard version:
> DELETE FROM table1
> WHERE EXISTS (SELECT *
> FROM table2
> WHERE table2.ID = table1.ID_Table2
> AND table2.value = 5)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)sql

Delete across tables

hi,

I have two tables that are related, ie I created them with;

create table cm_message (
msgid varchar(40) not null primary key,
location varchar(240),
ts timestamp default 'now' not null,
lastsent timestamp
);

create table cm_data (
pkey integer not null primary key,
subdata varchar(255),
msgid varchar(40),
foreign key (msgid) references cm_message(msgid)
);

basically for each entry in cm_message there can be several cm_data entries and they're linked using the msgid fields.

I'm trying to write a purge script that will delete entries (in cm_message and cm_data) that have a cm_message.ts timestamp older than n hours. Can I do a 'delete from ... where cm_message.ts > n' which does some kind of union between the two tables and delete entries from both tables at one stroke?

At the moment I'm looking at selecting all old entries from cm_message and deleting all in cm_data for each msgid, but there must be a more efficient way of using the relational stuff...

thanks,
nikUse the ON DELETE CASCADE option:
...foreign key (msgid) references cm_message(msgid) ON DELETE CASCADE :rolleyes:|||which database system is this? because i don't know of any that will support this --timestamp default 'now'|||thanks LKBrwn_DBA.

rudy, the database is firebird - I think it also accepts TODAY, TOMORROW and YESTERDAY which is nice and handy...

nik|||wow, ya learn sumpin new every day ;)

thanks nik|||Hi,

Does it work on a MySQL database?

//M|||Does it work on a MySQL database?the ON DELETE CASCADE? only for InnoDB tables

Delete a Database which was previously Replicated

Hello,
I have a database which i used to replicate it to a subscriber, but i
deleted subscriber and publication and everything that related to it.
I don't know why, when i want to delete it, i get the following error message:
"Error 3724: Cannot drop the database xxxxxx because it is being used for
replication"
Thanks in advance for your help !!!!!!!!
Mathew
Matthew,
please use sp_removedbrepication on the database.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Definition of 'Persistent Data'

What is the definition of 'Persistent Data' as related to the definition of a RDBMS?

thx,

Kat

Persistent Data is data that stays around after the application has terminated. In the case of an RDBMS, it would refer to data which had been committed to tables. It would not refer to items stored in temporary tables or units of work that the server accumulated while processing a query -- work tables. It would also not refer to variables defined and used during processing.

|||In a simple form it means saved to disk (not only used for data, any file could be persisted-saved).|||

hi kat,

i encounter the term "persistent data" in the .net terminology

which refers to data in the cache of an asp.net application

its in one of the three caching method of .net which are page, fragment and data caching.

you can also persist the data from the dataset to a XML file

oh well, terms are used in different ways and honestly

i dont have any idea about the subject matter.

well, just wanna say hi!

anyway, here are some usefull link

http://en.wikipedia.org/wiki/Persistent_data_structure

http://www.techweb.com/encyclopedia/defineterm.jhtml?term=persistent+data

joey

|||

Hello,
Data is said to be persistent in RDBMS when the transaction that has written it is commited. One of cornerstones of RDBMS is the ACID contract between the system and the components that issue transactions. ACID stands for
Atomicity, Consistency, Isolation, Durability. The Durability property sais that once a transaction has been commited, its modifications are guaranteed to survive any failure of system, once the permanent storage is not damaged. Another term for Duarbility is Persistence(they just use Durability because ACID is the cutest abbreviature one can think of :) )
So, basically, persistent data is the data that has been issued by a transaction which has already been commited. It is guaranteed to survive any system failures.

Regards,
Andranik

Defining an Action in SSAS

Hi,

I have the need to build an Action on a measure that belongs to a cube that behaves like a DrillThrough Action that returns details related to a measure.

The problem is that the details data should come from a table that is not inside the cube but it is in an SQLServer DB.

So, it is possible to define an Action that could retrieve data from an external DB?

If so, how I'll write the action in BIDS (Target Type, Action Type, Action expression, etc.)?.

Thank you.

You would need to create a rowset action. I don't have sample code, but hopefully someone else will be able to provide you with that information.

|||

Thank you for the answer,

but you said to create a rowset action, can I use a "statement" action instead?

I mean, is there any reason that let you suggest me to use a rowset action instead of a statement action?

Please let me understand better. Thank you!