cascading - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

cascading

Author

Topic

gunjansr83
Starting Member

21 Posts

Posted - 2008-09-17 : 11:06:58

Hi folks,

i am trying to do cascading with a table, but unable to do that....
what i want when i remove theme from table it will replace with default...plaese find the code

create table Users
(
UserID int primary key,
UserName varchar(100),
ThemeID int default 1 constraint Users_ThemeID_FK
references Themes(ThemeID) on delete set default
)

its not working...plz help

Gunjan Singh Rathore

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-17 : 11:58:50

Please have a read
http://www.sqlteam.com/article/using-set-null-and-set-default-with-foreign-key-constraints

E 12°55'05.63"
N 56°04'39.26"

gunjansr83
Starting Member

21 Posts

Posted - 2008-09-17 : 13:05:17

boss but this is not working............try & compile it mate

Gunjan Singh Rathore

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-17 : 13:09:11

quote:
Originally posted by gunjansr83

boss but this is not working............try & compile it mate

Gunjan Singh Rathore

Are you using sql 2005?

james_wells
Yak Posting Veteran

55 Posts

Posted - 2008-09-17 : 18:49:37

i currently agree with gunjansr83 that there is an issue
not sure myself YET what is causing the problem

if you script the table to a sql windows then the table has been
modified correctly.

i have the privilege to do anything
version 9.0.4027

using sql 2005

if the code below works on anyone else's sql 2005 then
it must be a patch issue

interested if anyone else has the same issue or not
in the mean time i have other servers that i can try the same
coding

------ example code ---------

create table Themes
(
ThemeID int primary key,
ThemeName varchar(100),
)

create table Users
(
UserID int primary key,
UserName varchar(100),
ThemeID int default 1 constraint Users_ThemeID_FK
references Themes(ThemeID) on delete set default
)

INSERT INTO Themes values(10,'theme 10')
GO
INSERT INTO Themes values(20,'theme 20')
GO

INSERT INTO Users values(1234,'FRED', 10)
GO
INSERT INTO Users values (3333,'SALLY',10)
GO
INSERT INTO Users values(9999,'GILBERT',10)
GO
INSERT INTO Users values(2222,'HARRY',20)
GO

delete from Themes
where ThemeID = 10

Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the FOREIGN KEY constraint "Users_ThemeID_FK". The conflict occurred in database "JWDB", table "dbo.Themes", column 'ThemeID'.
The statement has been terminated

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-09-17 : 18:52:59

quote:
Originally posted by james_wells

create table Users
(
UserID int primary key,
UserName varchar(100),
ThemeID int default 1 constraint Users_ThemeID_FK
references Themes(ThemeID) on delete set default
)

INSERT INTO Themes values(10,'theme 10')
GO
INSERT INTO Themes values(20,'theme 20')
GO

Umm, there is not Theme with ID 1...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-18 : 00:14:34

As Lamprey pointed out its because you've given default value as 1 which still is not a valid entry in themes table. Try giving default value as one which exists in themes, otherwise foreign key will be violated when it tries to assign default value.

james_wells
Yak Posting Veteran

55 Posts

Posted - 2008-09-18 : 04:21:18

Driving into work this morning the penny dropped
what i was doing wrong ( a grey momement i think)

The default value must be a value that exists on the file
that is referenced - yes i know - this is obvious when you actual think about it.

Unfortunately for me , you guys had already pointed it out.

gunjansr83
Starting Member

21 Posts

Posted - 2008-09-18 : 05:34:31

Please check themes table and its value, as per my post comment i specify tht 1 is a default value & even i put that value in themes table........folks something is wrong with user code....

create table Themes
(
ThemeID int primary key,
ThemeName varchar(100),
)

insert into Themes (ThemeID, ThemeName) values (1,'Default')
insert into Themes (ThemeID, ThemeName) values (2,'Winter')

create table Users
(
UserID int primary key,
UserName varchar(100),
ThemeID int default 1 constraint Users_ThemeID_FK
references Themes(ThemeID) on delete set default
)
go

user table is running wondering the best solution

Gunjan Singh Rathore

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-18 : 05:49:16

quote:
Originally posted by gunjansr83

Please check themes table and its value, as per my post comment i specify tht 1 is a default value & even i put that value in themes table........folks something is wrong with user code....

create table Themes
(
ThemeID int primary key,
ThemeName varchar(100),
)

insert into Themes (ThemeID, ThemeName) values (1,'Default')
insert into Themes (ThemeID, ThemeName) values (2,'Winter')

create table Users
(
UserID int primary key,
UserName varchar(100),
ThemeID int default 1 constraint Users_ThemeID_FK
references Themes(ThemeID) on delete set default
)
go

user table is running wondering the best solution

Gunjan Singh Rathore

are you using sql 2005?

gunjansr83
Starting Member

21 Posts

Posted - 2008-09-18 : 08:29:49

No.....THATS SQL SERVER 2000

Gunjan Singh Rathore

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-18 : 10:03:02

quote:
Originally posted by gunjansr83

No.....THATS SQL SERVER 2000

Gunjan Singh Rathore

thats the problem
You've on delete set default option only from sql 2005 onwards

gunjansr83
Starting Member

21 Posts

Posted - 2008-09-18 : 10:15:56

IS THERE A WAY TO SET DEFAULT VALUE IN SQL SERVER 2000.I NEED THAT BOSS

Gunjan Singh Rathore

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-18 : 10:25:50

quote:
Originally posted by gunjansr83

IS THERE A WAY TO SET DEFAULT VALUE IN SQL SERVER 2000.I NEED THAT BOSS

Gunjan Singh Rathore

You mean with foreignkey on delete? if yes, nope you cant. What you can alternatively do is to write a trigger to achieve this purpose.

Also,Can i ask you not to use caps while posting?

gunjansr83
Starting Member

21 Posts

Posted - 2008-09-18 : 11:23:03

I am create this one but its not working....plz help

create trigger tr_setdefault
on themes
after delete
as
if (select * from deleted where themeid = '1')
begin
insert into themes (themeid, themename) values (1,'default')
end
else
begin
update users set themeid = '1' where themeid not in (select themeid from themes)
end
go

Gunjan Singh Rathore

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-18 : 11:38:02

quote:
Originally posted by gunjansr83

I am create this one but its not working....plz help

create trigger tr_setdefault
on themes
after delete
as
if (select * from deleted where themeid = '1')
begin
insert into themes (themeid, themename) values (1,'default')
end
else
begin
update users set themeid = '1' where themeid not in (select themeid from themes)
end
go

Gunjan Singh Rathore

i think what you want is instead of trigger

create trigger tr_setdefault
on themes
instead of  delete
as
update u
set u.ThemeID=DEFAULT
FROM Users u
INNER JOIN DELETED d
ON d.ThemeID=u.ThemeID

DELETE t
FROM Themes t
INNER JOIN DELETED d
ON d.ThemeID=t.ThemeID

gunjansr83
Starting Member

21 Posts

Posted - 2008-09-18 : 13:38:02

Though its compile but unable to execute it, it gives an error

Server: Msg 547, Level 16, State 1, Procedure tr_setdefault, Line 6
UPDATE statement conflicted with COLUMN FOREIGN KEY constraint 'Users_ThemeID_FK'. The conflict occurred in database 'GUNJAN', table 'Themes', column 'ThemeID'.
The statement has been terminated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-18 : 13:41:34

quote:
Originally posted by gunjansr83

Though its compile but unable to execute it, it gives an error

Server: Msg 547, Level 16, State 1, Procedure tr_setdefault, Line 6
UPDATE statement conflicted with COLUMN FOREIGN KEY constraint 'Users_ThemeID_FK'. The conflict occurred in database 'GUNJAN', table 'Themes', column 'ThemeID'.
The statement has been terminated.

what have you set as DEFAULT value of ThemeID in Users table? does that value exist as a record in Themes table?

gunjansr83
Starting Member

21 Posts

Posted - 2008-09-18 : 13:55:49

i cannot set default value in theme table i want to set it in user table.........but for refrence i want set 1 as default themeid in users table

Gunjan Singh Rathore

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-18 : 14:00:41

quote:
Originally posted by gunjansr83

i cannot set default value in theme table i want to set it in user table.........but for refrence i want set 1 as default themeid in users table

Gunjan Singh Rathore

i know that. i asked what you set as default in users?
Only thing you should make sure is that the default value you give in users should be existing in themes.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-18 : 14:01:48

Don't the article explain this?

E 12°55'05.63"
N 56°04'39.26"

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources