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)
);http://www.schemamania.org/jkl/booksonline/SQLBOL70/html/7_design_41.htm for detailed explanation.
Also refer to books online for additional information.|||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
);|||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
No comments:
Post a Comment