On delete cascade

I faced this issue when using ON DELETE CASCADE functionality. Please
let me if this can be termed as a bug.

create table test(
    num    integer,
    primary key (num)
);

create table test_assoc(
    num1    integer,
    num2    integer,
    primary key(num1, num2),
    foreign key num1 references test(num) on delete cascade, *-- NOTE:
CASCADE is specified here.*
    foreign key num2 references test(num) on delete cascade, );

My assumption was if i delete any row from the test table, then all the
rows which are affected by this will get deleted automatically from the
test_assoc table.
Unfortunately this isn't happening.

But, if i say,

create table test_assoc(
    num1    integer references test(num) on delete cascade, *-- NOTE:
CASCADE is specified here.*
    num2    integer references test(num) on delete cascade,
    primary key(num1, num2)
);

It works.

Regards,
Annie

0
annette
12/15/2006 12:25:18 PM
sybase.sqlanywhere.general 32637 articles. 4 followers. Follow

3 Replies
590 Views

Similar Articles

[PageSpeed] 53

The first form of the declarations of the foreign keys in test_assoc causes 
a column called "num" to be added to the table and used for the foreign key 
(not what you intended). Instead, the statement should have been:

create table test_assoc(
    num1    integer,
    num2    integer,
    primary key(num1, num2),
    foreign key num1 (num1) references test(num) on delete cascade,
    foreign key num2 (num2) references test(num) on delete cascade
);

Please see the description of the FOREIGN KEY constraint clause in the 
CREATE TABLE section of the documentation.

Whitepapers, TechDocs, bug fixes are all available through the iAnywhere 
Developer Community at http://www.ianywhere.com/developer

<annette.benz@gmail.com> wrote in message 
news:1166185518.518861.205510@73g2000cwn.googlegroups.com...
>I faced this issue when using ON DELETE CASCADE functionality. Please
> let me if this can be termed as a bug.
>
> create table test(
>    num    integer,
>    primary key (num)
> );
>
> create table test_assoc(
>    num1    integer,
>    num2    integer,
>    primary key(num1, num2),
>    foreign key num1 references test(num) on delete cascade, *-- NOTE:
> CASCADE is specified here.*
>    foreign key num2 references test(num) on delete cascade, );
>
> My assumption was if i delete any row from the test table, then all the
> rows which are affected by this will get deleted automatically from the
> test_assoc table.
> Unfortunately this isn't happening.
>
> But, if i say,
>
> create table test_assoc(
>    num1    integer references test(num) on delete cascade, *-- NOTE:
> CASCADE is specified here.*
>    num2    integer references test(num) on delete cascade,
>    primary key(num1, num2)
> );
>
> It works.
>
> Regards,
> Annie
> 


0
Bruce
12/15/2006 1:36:58 PM
On 19 Dec 2006 08:32:27 -0800, "JoeMellem" <joemellem@gmail.com>
wrote:

>The CASCADE clause belongs on the primary table and the changes cascade
>downward to the dependent table.  So, you only need the clause on table
>"test".

That is not correct. While CASCADE is driven by actions performed on
the primary table, the clause itself goes on the dependent table, in
this case "test_assoc".

Breck

On 19 Dec 2006 08:32:27 -0800, "JoeMellem" <joemellem@gmail.com>
wrote:

>Annette,
>
>The CASCADE clause belongs on the primary table and the changes cascade
>downward to the dependent table.  So, you only need the clause on table
>"test".
>
>Regards,
>
>Joe Mellem
>
>annette.benz@gmail.com wrote:
>> I faced this issue when using ON DELETE CASCADE functionality. Please
>> let me if this can be termed as a bug.
>>
>> create table test(
>>     num    integer,
>>     primary key (num)
>> );
>>
>> create table test_assoc(
>>     num1    integer,
>>     num2    integer,
>>     primary key(num1, num2),
>>     foreign key num1 references test(num) on delete cascade, *-- NOTE:
>> CASCADE is specified here.*
>>     foreign key num2 references test(num) on delete cascade, );
>>
>> My assumption was if i delete any row from the test table, then all the
>> rows which are affected by this will get deleted automatically from the
>> test_assoc table.
>> Unfortunately this isn't happening.
>>
>> But, if i say,
>>
>> create table test_assoc(
>>     num1    integer references test(num) on delete cascade, *-- NOTE:
>> CASCADE is specified here.*
>>     num2    integer references test(num) on delete cascade,
>>     primary key(num1, num2)
>> );
>> 
>> It works.
>> 
>> Regards,
>> Annie

--
Breck Carter [Team iAnywhere]
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
The book: http://www.risingroad.com/SQL_Anywhere_Studio_9_Developers_Guide.html
breck.carter@risingroad.com
0
Breck
12/19/2006 4:28:04 PM
Annette,

The CASCADE clause belongs on the primary table and the changes cascade
downward to the dependent table.  So, you only need the clause on table
"test".

Regards,

Joe Mellem

annette.benz@gmail.com wrote:
> I faced this issue when using ON DELETE CASCADE functionality. Please
> let me if this can be termed as a bug.
>
> create table test(
>     num    integer,
>     primary key (num)
> );
>
> create table test_assoc(
>     num1    integer,
>     num2    integer,
>     primary key(num1, num2),
>     foreign key num1 references test(num) on delete cascade, *-- NOTE:
> CASCADE is specified here.*
>     foreign key num2 references test(num) on delete cascade, );
>
> My assumption was if i delete any row from the test table, then all the
> rows which are affected by this will get deleted automatically from the
> test_assoc table.
> Unfortunately this isn't happening.
>
> But, if i say,
>
> create table test_assoc(
>     num1    integer references test(num) on delete cascade, *-- NOTE:
> CASCADE is specified here.*
>     num2    integer references test(num) on delete cascade,
>     primary key(num1, num2)
> );
> 
> It works.
> 
> Regards,
> Annie

0
JoeMellem
12/19/2006 4:32:27 PM
Reply:

Similar Artilces:

Cascading deletes - Which is better
I need to implement my cascading deletes on a SQL database.  Is it better (performance/reliablility-wise) to use the Foreign Key Cascading Deletes or to just write my own triggers to do the deletes?I was hoping someone had experimented and found which works best. DRI(declarative referential integrity) triggers are used when Cascade Deletes and Updates cannot be implemented in the RDBMS(relational database management system) as in SQL Server 7.0 and below.  In SQL Server 2000 using Cascade Delete and Cascade Update is the way to go.  The reason is reliability related because ...

Is Cascading deletes prefered to deleting thru a stored procedure?
I have a situation in which I have three tables - 'Table1', 'Table2' and 'Table3'. 'Table1' is a primary table for 'Table2' so that the the primary key of 'Table1' is a foreign key in 'Table2'. In the same way, 'Table1' is also the primary table for 'Table3'. If I delete a row in 'Table1' then I could automatically let SQL Server also delete the related rows in 'Table2' and 'Table3' through 'cascading delete' option. Or I could run a trigger or stored procedure for deleting related rows in 'Table2' and 'Table3'. Which of these methods would be quicker? Also would cascading deletes option a...

Cascading deletes
I need to know the best method to process cascading deletes with a SQL Anywhere 5.0 DB. The user can delete certain records and I want to make sure all child records are also deleted. Thanks in advance. The easiest thing to do is to use the build in cascading deletes option of foreign keys. Geert 'Darling' Van Damme Jason Csomay wrote in message ... >I need to know the best method to process cascading deletes with a SQL >Anywhere 5.0 DB. The user can delete certain records and I want to make >sure all child records are also deleted. > > ...

Cascading delete
In my consolidated database I have a foreign key between two tables with the cascading delete integrity constraint applied. The two tables are replicated (in their entirety) to a remote database. In the remote database should I have the delete integrity constraint applied in the foreign key between the two tables? If a record is deleted from table A in the consolidated database and this cascades and deletes a record in table B, won't the individual delete statements from the transaction log be applied to the remote database thus removing the need for the "cascade" to ...

delete cascade
I'm trying to add delete cascade to an already-existing table via ISQL. I'm getting a syntax error: alter table carrier on delete cascade; Error = syntax error near 'on' Should I be able to do this? I'm running on Windows 2000 Pro, SQLAnywhere 5.5.03, using DBENG50.EXE. It should be something like: alter table carrier drop foreign key fk1 alter table carrier add foreign key fk1 references ... etc ... on delete cascade Then when the row in the fk1 table is deleted the row in the "carrier" table will be deleted also. -- Steve http://www...

Delete on cascade
Hello, I have a problem with the speed of a delete on a table that use cascades delete I use ASA 7.0.2.1493 I have a table (tb_a). It has 100 records. This table is referenced by 16 other tables. (tb_b) Some of these tables are referenced by other tables.(tb_c) Some of these referenced tables are referenced by other tables. (tb_d) Sometimes, tables are referencing other tables directly ex : tb_a is referenced by tb_d When I make a delete on tb_a, it takes 30 sec. to make the delete. The delete is indexed as I delete on the primary key from tb_a. For tb_b, tb_c, tb_c, There ...

CASCADE ON DELETE
Hi, Just a quick question: will ASE 12.5 support CASCADE ON DELETE on table creation? Take care, Yicheng ...

cascade delete
Does anyone know how to implement cascade delete within ASE on tables with foreign keys? If so please help me! I know that ASA has this capability but I can not find anything in ASE to support it. Here is my email if you have an answer pitsnogle@va.prestige.net thanks, Richard ASE does not support declarative cascading deletes through referential integrity. If you want to do cascading deletes, you will have to write triggers to handle your foreign keys, rather than using the constraints. -bret Rich and Peg wrote: > Does anyone know how to implement cascade dele...

Cascading deletes
Were using these right? Noticed that there is no DeleteBooks method, but a delete contentPage method - I swear to god that we need to have a ContentItem class - I know there is a ContentItem class defined under that darn ContentList repeater, But I renamed mine to ContentItemContainer I think that we should have a Content Item table in the DB the HorrorGet ready to blast your energy levels into high gear with Sedgewick Energy Drink. High powered adaptogenic herbs mixed with potent levels of B-vitamins and...Please disregard the contents of this post....

Deletes are not being deleted.
Using ASA 7.03 I am having a problem in deleting field service orders that have been completed. Just getting started with sync so I am probably making a dumb mistake or just leaving out a step. The download_delete_cursor script is: Select ordernumber from ServiceOrderEntryMaster where Tech = ? and Order_Complete = 'Y' This based on the verbose reply from the MobiLink Synchronization server is picking the correct work order ( number 326 ) and showing a Delete Row line in the log. But the work order is not being deleted on the remote side. What could I be doing wron...

On Delete Cascade
Hi all, I'm working on the porting of an application from Oracle to Sybase (ASE 11.9.2). Now I'm facing with the "on delete cascade" and "cascade constraints" directives present in Oracle. Is there anything similar in Sybase or is there any workaround? Thanks to anybody can help me. Gianpaolo. -- ------------------------------------------------------------------ Gianpaolo Araco phone +39-06-47823776 - ext: 17 Web Bridges Srl fax +39-06-48906270 Via Mecenate 59 mailto:gianpaolo@web...

on delete cascade?
This is a multi-part message in MIME format. --------------DB2070C890FF2F3A31CE38D9 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Hi, I'm new to Sybase SQL Server 11 and I'm wondering whether there is any possibility to perform a cascading delete in a master-detail-relationship using a foreign key constraint? Any ideas? Thanks Thomas --------------DB2070C890FF2F3A31CE38D9 Content-Type: text/x-vcard; charset=us-ascii; name="vcard.vcf" Content-Transfer-Encoding: 7bit Content-Description: Card for Thomas Golob Content-Dispositi...

cascading delete
This is a multi-part message in MIME format. ------=_NextPart_000_000A_01C0C26F.36B25870 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I'm using SYBASE Server 11 and Powerbuilder 7.02. I'm having = problems with a trigger on a parent child relationship of two tables, = FISH_TBL and FISH_LEN_TBL, with a foriegn key constraint on FISH_LEN_TBL = using the primary key, FISH_ID from FISH_TBL. =20 To allow users to Delete a FISH_TBL record I have a trigger on = delete for FISH_TBL that will delete all length rec...

CASCADE deletes
In SQL Central, how do I set sqlanywhere to enforce referential integrety and delete all foreign keys when a record is deleted? Thanks for any help, Randy Nelson >In SQL Central, how do I set sqlanywhere to enforce referential integrety >and delete all foreign keys when a record is deleted? To create a foreign key you can click on the "Add foreign Key" line to start a fkey creation wizard. To change the properties of an existing foreign key definition, Right click on the foreign key and choose Properties. Click the 'Integrity' tab. Under "de...

Cascading deletes
Thanks. So I understand that there is currently no cascading deletes in MS SQL Server and you implement it using triggers or similar. Correct. There is a LOT of stuff missing from MS SQL Server. You will be much happier with SQL Anywhere. Breck On Tue, 26 Dec 2000 11:23:39 -0800, "Anand" <aav1234@hotmail.com> wrote: >Thanks. So I understand that there is currently no cascading deletes in MS >SQL Server and you implement it using triggers or similar. > ...

Web resources about - On delete cascade - sybase.sqlanywhere.general

Cascade Volcanoes - Wikipedia, the free encyclopedia
The Cascade Volcanoes (also known as the Cascade Volcanic Arc or the Cascade Arc ) are a number of volcanoes in a volcanic arc in western North ...

Pay-it-forward cascades circle the globe via Facebook
by Lada Adamic and Thomas Lento All data analysis was performed on anonymized data The idea of paying it forward is simple. A friend or stranger ...

Picasa Cascade for iPad on the iTunes App Store
Read reviews, get customer ratings, see screenshots, and learn more about Picasa Cascade on the App Store. Download Picasa Cascade and enjoy ...

Cascade Range Mountains : Just another landscape - Flickr - Photo Sharing!
After spending a wonderful time at lake Chelan I continued driving to cover the entire cascade loop and this was some place where I stopped on ...

Virus.DOS.Cascade - YouTube
The Cascade DOS virus is one of my favorites, and activates by forcing all of the letters on the screen to collapse to the bottom in a giant ...

Cascade Coal and NuCoal lose High Court bid to reinstate 'tainted' licences
Two mining companies stripped of their exploration rights following explosive corruption inquiries into Eddie Obeid and other NSW Labor figures ...

ICAC troubles Cascade as corruption findings are overturned
CORRUPTION findings against Cascade Coal directors involved in a $100 million mining venture with the Obeid family are set to be overturned in ...

Win for people power as Coca-Cola relaunches Cascade Blackcurrant Ultra-C
BEVERAGE giant Coca-Cola Amatil has announced a win for people power — or at least, the people of Tasmania — today relaunching the popular Cascade ...

'Cascade of diarrhoea': UK critic savages Australian art exhibition
Much of the so-called cream of Australian art is lightweight, provincial and dull, and some of it is reminiscent of liquid crap, says one of ...

Eurovision 2013 plagiarism probe over Cascade's Glorious
Germany's entry for this year's Eurovision Song Contest is being investigated after allegations that it copied elements of last year's winner, ...

Resources last updated: 12/26/2015 5:06:33 AM