Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004
    Posts
    19

    Exclamation Unanswered: How to alter the table with delete/update cascade without recreating the table

    I have contract table which has built in foreign key constrains. How can I alter this table for delete/ update cascade without recreating the table so whenever studentId/ contactId is modified, the change is effected to the contract table.

    Thanks


    ************************************************** ******
    Contract table DDL is

    create table contract(
    contractNum int identity(1,1) primary key,
    contractDate smalldatetime not null,
    tuition money not null,
    studentId char(4) not null foreign key references student (studentId),
    contactId int not null foreign key references contact (contactId)
    );

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    http://www.schemamania.org/jkl/books..._design_41.htm for detailed explanation.


    Also refer to books online for additional information.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  3. #3
    Join Date
    Apr 2004
    Posts
    19

    Exclamation it is not helping

    guys,
    the earlier response was not helping. Does anyone have better help?
    I tryed the new DDL but not working either. How can I alter my table or create new one to make the cascading working.

    thanks in advance!


    ************************************************** *
    My original contract table DDL is

    create table contract(
    contractNum int identity(1,1) primary key,
    contractDate smalldatetime not null,
    tuition money not null,
    studentId char(4) not null foreign key references student (studentId)
    contactId int not null foreign key references contact (contactId)
    );

    ************************************************** ***
    My modified contract table DDL is

    create table contract(
    contractNum int identity(1,1) primary key,
    contractDate smalldatetime not null,
    tuition money not null,
    studentId char(4) not null foreign key
    references student (studentId)
    on update cascade
    on delete cascade,
    contactId int not null foreign key
    references contact (contactId)
    on update cascade
    on delete cascade
    );

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    set nocount on
    begin tran
    if object_id('dbo.t1') is not null drop table dbo.t1
    go
    if object_id('dbo.t2') is not null drop table dbo.t2
    go
    create table dbo.t1 (f1 int not null primary key, f2 char(1) null)
    go
    create table dbo.t2 (f0 int not null primary key, f1 int not null constraint fk_t2 foreign key references dbo.t1(f1))
    go
    insert t1 select 1, 'A'
    go
    insert t2 select 1, f1 from t1
    go
    select * from t2
    go
    alter table t2 drop constraint fk_t2
    go
    alter table t2 add constraint fk_t2 foreign key (f1) references t1(f1) on update cascade
    go
    update t1 set f1 = 2
    go
    select * from t2
    go
    rollback tran
    go
    set nocount off
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •