Closed alex440440 closed 10 years ago
this is a basic test script and I get no results on it.
it runs in ms sql 2008 with the following results: 3 main3 1 3 1 sec3 1 2 main2 2 2 2 sec2 2 1 main1 3 1 3 sec1 3
why can it be?
IF OBJECT_ID('sec', 'U') IS NOT NULL DROP TABLE sec go
IF OBJECT_ID('interm', 'U') IS NOT NULL DROP TABLE interm go
IF OBJECT_ID('main', 'U') IS NOT NULL DROP TABLE main go
create table main( id int not null identity, descr nvarchar(100) )
go
alter table main add primary key (id) go
insert into main (descr) values ('main1'),('main2'),('main3') go
create table sec( id int not null identity, main_id int not null, slave_name nvarchar(100) )
alter table sec add constraint fk_sec_main_id FOREIGN KEY (main_id) REFERENCES main(id) go
insert into sec(main_id,slave_name)values (3,'sec3'),(2,'sec2'),(1,'sec1')
create table interm ( id int not null identity, main_id int not null, )
alter table interm add primary key (id) go
insert into interm (main_id) select main_id from sec go
alter table sec drop constraint fk_sec_main_id go
alter table sec drop column main_id go
alter table sec add interm_id int go
alter table sec add constraint fk_interm_mainId foreign key (interm_id) references interm(id) go
update sec set interm_id=id go
select * from main join interm on main.id=interm.main_id join sec on sec.interm_id=interm.id go
Works fine for me : http://sqlfiddle.com/#!3/13eac/2
this is a basic test script and I get no results on it.
it runs in ms sql 2008 with the following results: 3 main3 1 3 1 sec3 1 2 main2 2 2 2 sec2 2 1 main1 3 1 3 sec1 3
why can it be?
IF OBJECT_ID('sec', 'U') IS NOT NULL DROP TABLE sec go
IF OBJECT_ID('interm', 'U') IS NOT NULL DROP TABLE interm go
IF OBJECT_ID('main', 'U') IS NOT NULL DROP TABLE main go
create table main( id int not null identity, descr nvarchar(100) )
go
alter table main add primary key (id) go
insert into main (descr) values ('main1'),('main2'),('main3') go
create table sec( id int not null identity, main_id int not null, slave_name nvarchar(100) )
go
alter table sec add constraint fk_sec_main_id FOREIGN KEY (main_id) REFERENCES main(id) go
insert into sec(main_id,slave_name)values (3,'sec3'),(2,'sec2'),(1,'sec1')
go
create table interm ( id int not null identity, main_id int not null, )
go
alter table interm add primary key (id) go
insert into interm (main_id) select main_id from sec go
alter table sec drop constraint fk_sec_main_id go
alter table sec drop column main_id go
alter table sec add interm_id int go
alter table sec add constraint fk_interm_mainId foreign key (interm_id) references interm(id) go
update sec set interm_id=id go
select * from main join interm on main.id=interm.main_id join sec on sec.interm_id=interm.id go