updating text datatype: schema

(SQL Server 2000, SP3)
Hello all!
I am trying to use ERwin to create a new schema. In the past, because of limitations with
SQL Server's declarative referential integrity (DRI), I typically did all my RI through
triggers. I thought I'd take the plunge and try using DRI with a
Parent-Cascade/Child-Restrict paradigm.
When I generate the script, I get the following:
CREATE TABLE amContainer (
ContainerId int IDENTITY(1, 1),
ParentContainerId int NULL,
TypeContainerId int NOT NULL,
CONSTRAINT XPKamContainer
PRIMARY KEY (ContainerId)
)
go
CREATE TABLE amTypeContainer (
TypeContainerId int IDENTITY(1, 1),
Name varchar(255) NOT NULL,
CONSTRAINT XPKamTypeContainer
PRIMARY KEY (TypeContainerId)
)
go
ALTER TABLE amContainer
ADD CONSTRAINT R_3
FOREIGN KEY (TypeContainerId)
REFERENCES amTypeContainer
ON DELETE CASCADE
ON UPDATE CASCADE
go
ALTER TABLE amContainer
ADD CONSTRAINT R_1
FOREIGN KEY (ParentContainerId)
REFERENCES amContainer
ON UPDATE CASCADE
go
I'm getting an error on R_1 (that last ALTER TABLE command):
Server: Msg 1785, Level 16, State 1, Line 3
Introducing FOREIGN KEY constraint 'R_1' on table 'amContainer' may cause cycles or
multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify
other FOREIGN KEY constraints.
Server: Msg 1750, Level 16, State 1, Line 3
Could not create constraint. See previous errors.
Is this not possible with DRI? Do I need to resort to triggers to apply what I want?
Thanks for any help you can provide!
John PetersonThis is a multi-part message in MIME format.
--=_NextPart_000_015B_01C3618E.3E6B8530
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
You cannot use DRI for cascades on a self-referencing table. For this, =you will need a trigger.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message =news:etENJ7aYDHA.2572@.TK2MSFTNGP09.phx.gbl...
(SQL Server 2000, SP3)
Hello all!
I am trying to use ERwin to create a new schema. In the past, because =of limitations with
SQL Server's declarative referential integrity (DRI), I typically did =all my RI through
triggers. I thought I'd take the plunge and try using DRI with a
Parent-Cascade/Child-Restrict paradigm.
When I generate the script, I get the following:
CREATE TABLE amContainer (
ContainerId int IDENTITY(1, 1),
ParentContainerId int NULL,
TypeContainerId int NOT NULL,
CONSTRAINT XPKamContainer
PRIMARY KEY (ContainerId)
)
go
CREATE TABLE amTypeContainer (
TypeContainerId int IDENTITY(1, 1),
Name varchar(255) NOT NULL,
CONSTRAINT XPKamTypeContainer
PRIMARY KEY (TypeContainerId)
)
go
ALTER TABLE amContainer
ADD CONSTRAINT R_3
FOREIGN KEY (TypeContainerId)
REFERENCES amTypeContainer
ON DELETE CASCADE
ON UPDATE CASCADE
go
ALTER TABLE amContainer
ADD CONSTRAINT R_1
FOREIGN KEY (ParentContainerId)
REFERENCES amContainer
ON UPDATE CASCADE
go
I'm getting an error on R_1 (that last ALTER TABLE command):
Server: Msg 1785, Level 16, State 1, Line 3
Introducing FOREIGN KEY constraint 'R_1' on table 'amContainer' may =cause cycles or
multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO =ACTION, or modify
other FOREIGN KEY constraints.
Server: Msg 1750, Level 16, State 1, Line 3
Could not create constraint. See previous errors.
Is this not possible with DRI? Do I need to resort to triggers to apply =what I want?
Thanks for any help you can provide!
John Peterson
--=_NextPart_000_015B_01C3618E.3E6B8530
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

You cannot use DRI for cascades on a self-referencing table. For this, you will need a =trigger.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"John Peterson" wrote in =message news:etENJ7aYDHA.2572=@.TK2MSFTNGP09.phx.gbl...(SQL Server 2000, SP3)Hello all!I am trying to use ERwin to =create a new schema. In the past, because of limitations withSQL =Server's declarative referential integrity (DRI), I typically did all my RI throughtriggers. I thought I'd take the plunge and try using =DRI with aParent-Cascade/Child-Restrict paradigm.When I generate the =script, I get the following:CREATE TABLE amContainer ( ContainerId int IDENTITY(1, 1), ParentContainerId int NULL, TypeContainerId int NOT NULL, CONSTRAINT XPKamContainer &=nbsp; PRIMARY KEY (ContainerId))goCREATE TABLE amTypeContainer =( TypeContainerId int IDENTITY(1, 1), Name &nb=sp; varchar(255) NOT NULL, =CONSTRAINT XPKamTypeContainer &nb=sp; PRIMARY KEY (TypeContainerId))goALTER TABLE amContainer ADD CONSTRAINT R_3 = FOREIGN KEY (TypeContainerId) &nbs=p; =; REFERENCES amTypeContainer = &=nbsp; ON DELETE CASCADE &n=bsp; &nb=sp; ON UPDATE CASCADEgoALTER TABLE amContainer ADD CONSTRAINT R_1 = FOREIGN KEY (ParentContainerId) &n=bsp; &nb=sp; REFERENCES amContainer &nbs=p; =; ON UPDATE CASCADEgoI'm getting an error on R_1 (that =last ALTER TABLE command):Server: Msg 1785, Level 16, State 1, Line 3Introducing FOREIGN KEY constraint 'R_1' on table 'amContainer' may =cause cycles ormultiple cascade paths. Specify ON DELETE NO ACTION or ON =UPDATE NO ACTION, or modifyother FOREIGN KEY constraints.Server: Msg 1750, =Level 16, State 1, Line 3Could not create constraint. See previous errors.Is this not possible with DRI? Do I need to resort =to triggers to apply what I want?Thanks for any help you can provide!John Peterson

--=_NextPart_000_015B_01C3618E.3E6B8530--|||This is a multi-part message in MIME format.
--=_NextPart_000_002D_01C36176.F30B8700
Content-Type: multipart/alternative;
boundary="--=_NextPart_001_002E_01C36176.F30B8700"
--=_NextPart_001_002E_01C36176.F30B8700
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Tom, I'm still having trouble with other aspects of DRI. Please see the =attached DDL -- this time it's with my R_8 relationship. I don't quite ="grok" why this is problematic. (It's not as clear-cut as a =self-referencing table.)
Any additional help you can provide would be greatly appreciated!
John Peterson
"John Peterson" <j0hnp@.comcast.net> wrote in message =news:%23BK4wDbYDHA.440@.tk2msftngp13.phx.gbl...
Thanks, Tom! Maybe I can "mix and match" and only use triggers where =necessary.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:OPg2l$aYDHA.1640@.TK2MSFTNGP10.phx.gbl...
You cannot use DRI for cascades on a self-referencing table. For =this, you will need a trigger.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message =news:etENJ7aYDHA.2572@.TK2MSFTNGP09.phx.gbl...
(SQL Server 2000, SP3)
Hello all!
I am trying to use ERwin to create a new schema. In the past, =because of limitations with
SQL Server's declarative referential integrity (DRI), I typically =did all my RI through
triggers. I thought I'd take the plunge and try using DRI with a
Parent-Cascade/Child-Restrict paradigm.
When I generate the script, I get the following:
CREATE TABLE amContainer (
ContainerId int IDENTITY(1, 1),
ParentContainerId int NULL,
TypeContainerId int NOT NULL,
CONSTRAINT XPKamContainer
PRIMARY KEY (ContainerId)
)
go
CREATE TABLE amTypeContainer (
TypeContainerId int IDENTITY(1, 1),
Name varchar(255) NOT NULL,
CONSTRAINT XPKamTypeContainer
PRIMARY KEY (TypeContainerId)
)
go
ALTER TABLE amContainer
ADD CONSTRAINT R_3
FOREIGN KEY (TypeContainerId)
REFERENCES amTypeContainer
ON DELETE CASCADE
ON UPDATE CASCADE
go
ALTER TABLE amContainer
ADD CONSTRAINT R_1
FOREIGN KEY (ParentContainerId)
REFERENCES amContainer
ON UPDATE CASCADE
go
I'm getting an error on R_1 (that last ALTER TABLE command):
Server: Msg 1785, Level 16, State 1, Line 3
Introducing FOREIGN KEY constraint 'R_1' on table 'amContainer' may =cause cycles or
multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO =ACTION, or modify
other FOREIGN KEY constraints.
Server: Msg 1750, Level 16, State 1, Line 3
Could not create constraint. See previous errors.
Is this not possible with DRI? Do I need to resort to triggers to =apply what I want?
Thanks for any help you can provide!
John Peterson
--=_NextPart_001_002E_01C36176.F30B8700
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Tom, I'm still having trouble with =other aspects of DRI. Please see the attached DDL -- this time it's with my R_8 relationship. I don't quite "grok" why this is problematic. =(It's not as clear-cut as a self-referencing table.)
Any additional help you can provide =would be greatly appreciated!
John Peterson
"John Peterson" wrote in =message news:%23BK4wDbYDHA.4=40@.tk2msftngp13.phx.gbl...
Thanks, Tom! Maybe I can ="mix and match" and only use triggers where necessary.
"Tom Moreau" = wrote in message news:OPg2l$aYDHA.1640=@.TK2MSFTNGP10.phx.gbl...
You cannot use DRI for cascades on =a self-referencing table. For this, you will need a trigger.
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql

"John Peterson" wrote in =message news:etENJ7aYDHA.2572=@.TK2MSFTNGP09.phx.gbl...(SQL Server 2000, SP3)Hello all!I am trying to use ERwin =to create a new schema. In the past, because of limitations =withSQL Server's declarative referential integrity (DRI), I typically did =all my RI throughtriggers. I thought I'd take the plunge and try =using DRI with aParent-Cascade/Child-Restrict paradigm.When I =generate the script, I get the following:CREATE TABLE amContainer ( ContainerId =int IDENTITY(1, 1), ParentContainerId int NULL, TypeContainerId int NOT NULL, CONSTRAINT =XPKamContainer &=nbsp; PRIMARY KEY (ContainerId))goCREATE TABLE =amTypeContainer ( TypeContainerId int IDENTITY(1, 1), =Name &nb=sp; varchar(255) NOT NULL, =CONSTRAINT =XPKamTypeContainer &nb=sp; PRIMARY KEY (TypeContainerId))goALTER TABLE amContainer ADD CONSTRAINT =R_3 = FOREIGN KEY =(TypeContainerId) &nbs=p; =; REFERENCES =amTypeContainer = &=nbsp; ON DELETE =CASCADE &n=bsp; &nb=sp; ON UPDATE CASCADEgoALTER TABLE amContainer ADD CONSTRAINT =R_1 = FOREIGN KEY =(ParentContainerId) &n=bsp; &nb=sp; REFERENCES =amContainer &nbs=p; =; ON UPDATE CASCADEgoI'm getting an error on R_1 (that =last ALTER TABLE command):Server: Msg 1785, Level 16, State 1, =Line 3Introducing FOREIGN KEY constraint 'R_1' on table 'amContainer' =may cause cycles ormultiple cascade paths. Specify ON DELETE NO =ACTION or ON UPDATE NO ACTION, or modifyother FOREIGN KEY =constraints.Server: Msg 1750, Level 16, State 1, Line 3Could not create constraint. See =previous errors.Is this not possible with DRI? Do I need to =resort to triggers to apply what I want?Thanks for any help you can provide!John =Peterson

--=_NextPart_001_002E_01C36176.F30B8700--
--=_NextPart_000_002D_01C36176.F30B8700
Content-Type: text/plain;
name="Test.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="Test.sql"
CREATE TABLE amContainer (
ContainerId int IDENTITY(1, 1),
ParentContainerId int NULL,
TypeContainerId int NOT NULL,
CONSTRAINT XPKamContainer
PRIMARY KEY (ContainerId)
)
go
CREATE TABLE amMember (
MemberId int IDENTITY(1, 1),
ObjectId int NOT NULL,
ContainerId int NOT NULL,
TypeMemberAssociationId int NOT NULL,
CONSTRAINT XPKamMember
PRIMARY KEY (MemberId)
)
go
CREATE TABLE amObject (
ObjectId int IDENTITY(1, 1),
NativeObjectId uniqueidentifier NOT NULL,
TypeObjectId int NOT NULL,
CONSTRAINT XPKamObject
PRIMARY KEY (ObjectId)
)
go
CREATE TABLE amRights (
RightsId int IDENTITY(1, 1),
MemberId int NULL,
PartyObjectId int NULL,
Context varchar(255) NULL,
Rights varchar(255) NULL,
CONSTRAINT XPKamRights
PRIMARY KEY (RightsId)
)
go
CREATE TABLE amTypeContainer (
TypeContainerId int IDENTITY(1, 1),
Name varchar(255) NOT NULL,
CONSTRAINT XPKamTypeContainer
PRIMARY KEY (TypeContainerId)
)
go
CREATE TABLE amTypeMemberAssociation (
TypeMemberAssociationId int IDENTITY(1, 1),
Name varchar(255) NOT NULL,
Description varchar(255) NULL,
CONSTRAINT XPKamTypeMemberAssociation
PRIMARY KEY (TypeMemberAssociationId)
)
go
CREATE TABLE amTypeObject (
TypeObjectId int IDENTITY(1, 1),
Name varchar(255) NOT NULL,
TableSchema nvarchar(128) NULL,
TableName nvarchar(128) NULL,
TablePKColumn nvarchar(128) NULL,
CONSTRAINT XPKamTypeObject
PRIMARY KEY (TypeObjectId)
)
go
ALTER TABLE amContainer
ADD CONSTRAINT R_3
FOREIGN KEY (TypeContainerId)
REFERENCES amTypeContainer
ON DELETE CASCADE
ON UPDATE CASCADE
go
/*
ALTER TABLE amContainer
ADD CONSTRAINT R_1
FOREIGN KEY (ParentContainerId)
REFERENCES amContainer
ON UPDATE CASCADE
*/
go
ALTER TABLE amMember
ADD CONSTRAINT R_7
FOREIGN KEY (TypeMemberAssociationId)
REFERENCES amTypeMemberAssociation
ON DELETE CASCADE
ON UPDATE CASCADE
go
ALTER TABLE amMember
ADD CONSTRAINT R_6
FOREIGN KEY (ContainerId)
REFERENCES amContainer
ON DELETE CASCADE
ON UPDATE CASCADE
go
ALTER TABLE amMember
ADD CONSTRAINT R_5
FOREIGN KEY (ObjectId)
REFERENCES amObject
ON DELETE CASCADE
ON UPDATE CASCADE
go
ALTER TABLE amObject
ADD CONSTRAINT R_4
FOREIGN KEY (TypeObjectId)
REFERENCES amTypeObject
ON DELETE CASCADE
ON UPDATE CASCADE
go
ALTER TABLE amRights
ADD CONSTRAINT R_9
FOREIGN KEY (PartyObjectId)
REFERENCES amObject
ON DELETE CASCADE
ON UPDATE CASCADE
go
ALTER TABLE amRights
ADD CONSTRAINT R_8
FOREIGN KEY (MemberId)
REFERENCES amMember
ON DELETE CASCADE
ON UPDATE CASCADE
go
--=_NextPart_000_002D_01C36176.F30B8700--|||This is a multi-part message in MIME format.
--=_NextPart_000_01ED_01C36198.39729640
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
This type of problem is not limited to a self-referencing table. =Rather, it occurs any time there is a circular dependency. Already, =there R_9 establishes a relationship between amRights and amObject. R_5 =establishes the relationship between amObject and amMember. By adding =R_8 - between amRights and amMember - you have completed the circle.
The way to see this is to look at the ER diagram. It looks like you =should remove R_9 and install R_8.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message =news:uFC7lGbYDHA.2448@.TK2MSFTNGP09.phx.gbl...
Tom, I'm still having trouble with other aspects of DRI. Please see the =attached DDL -- this time it's with my R_8 relationship. I don't quite ="grok" why this is problematic. (It's not as clear-cut as a =self-referencing table.)
Any additional help you can provide would be greatly appreciated!
John Peterson
"John Peterson" <j0hnp@.comcast.net> wrote in message =news:%23BK4wDbYDHA.440@.tk2msftngp13.phx.gbl...
Thanks, Tom! Maybe I can "mix and match" and only use triggers where =necessary.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:OPg2l$aYDHA.1640@.TK2MSFTNGP10.phx.gbl...
You cannot use DRI for cascades on a self-referencing table. For =this, you will need a trigger.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message =news:etENJ7aYDHA.2572@.TK2MSFTNGP09.phx.gbl...
(SQL Server 2000, SP3)
Hello all!
I am trying to use ERwin to create a new schema. In the past, =because of limitations with
SQL Server's declarative referential integrity (DRI), I typically =did all my RI through
triggers. I thought I'd take the plunge and try using DRI with a
Parent-Cascade/Child-Restrict paradigm.
When I generate the script, I get the following:
CREATE TABLE amContainer (
ContainerId int IDENTITY(1, 1),
ParentContainerId int NULL,
TypeContainerId int NOT NULL,
CONSTRAINT XPKamContainer
PRIMARY KEY (ContainerId)
)
go
CREATE TABLE amTypeContainer (
TypeContainerId int IDENTITY(1, 1),
Name varchar(255) NOT NULL,
CONSTRAINT XPKamTypeContainer
PRIMARY KEY (TypeContainerId)
)
go
ALTER TABLE amContainer
ADD CONSTRAINT R_3
FOREIGN KEY (TypeContainerId)
REFERENCES amTypeContainer
ON DELETE CASCADE
ON UPDATE CASCADE
go
ALTER TABLE amContainer
ADD CONSTRAINT R_1
FOREIGN KEY (ParentContainerId)
REFERENCES amContainer
ON UPDATE CASCADE
go
I'm getting an error on R_1 (that last ALTER TABLE command):
Server: Msg 1785, Level 16, State 1, Line 3
Introducing FOREIGN KEY constraint 'R_1' on table 'amContainer' may =cause cycles or
multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO =ACTION, or modify
other FOREIGN KEY constraints.
Server: Msg 1750, Level 16, State 1, Line 3
Could not create constraint. See previous errors.
Is this not possible with DRI? Do I need to resort to triggers to =apply what I want?
Thanks for any help you can provide!
John Peterson
--=_NextPart_000_01ED_01C36198.39729640
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

This type of problem is not limited to =a self-referencing table. Rather, it occurs any time there is a =circular dependency. Already, there R_9 establishes a relationship between =amRights and amObject. R_5 establishes the relationship between amObject =and amMember. By adding R_8 - between amRights and amMember - you have =completed the circle.
The way to see this is to look at the =ER diagram. It looks like you should remove R_9 and install =R_8.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"John Peterson" wrote in =message news:uFC7lGbYDHA.2448=@.TK2MSFTNGP09.phx.gbl...
Tom, I'm still having trouble with =other aspects of DRI. Please see the attached DDL -- this time it's with my R_8 relationship. I don't quite "grok" why this is problematic. =(It's not as clear-cut as a self-referencing table.)
Any additional help you can provide =would be greatly appreciated!
John Peterson
"John Peterson" wrote in =message news:%23BK4wDbYDHA.4=40@.tk2msftngp13.phx.gbl...
Thanks, Tom! Maybe I can ="mix and match" and only use triggers where necessary.
"Tom Moreau" = wrote in message news:OPg2l$aYDHA.1640=@.TK2MSFTNGP10.phx.gbl...
You cannot use DRI for cascades on =a self-referencing table. For this, you will need a trigger.
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql

"John Peterson" wrote in =message news:etENJ7aYDHA.2572=@.TK2MSFTNGP09.phx.gbl...(SQL Server 2000, SP3)Hello all!I am trying to use ERwin =to create a new schema. In the past, because of limitations =withSQL Server's declarative referential integrity (DRI), I typically did =all my RI throughtriggers. I thought I'd take the plunge and try =using DRI with aParent-Cascade/Child-Restrict paradigm.When I =generate the script, I get the following:CREATE TABLE amContainer ( ContainerId =int IDENTITY(1, 1), ParentContainerId int NULL, TypeContainerId int NOT NULL, CONSTRAINT =XPKamContainer &=nbsp; PRIMARY KEY (ContainerId))goCREATE TABLE =amTypeContainer ( TypeContainerId int IDENTITY(1, 1), =Name &nb=sp; varchar(255) NOT NULL, =CONSTRAINT =XPKamTypeContainer &nb=sp; PRIMARY KEY (TypeContainerId))goALTER TABLE amContainer ADD CONSTRAINT =R_3 = FOREIGN KEY =(TypeContainerId) &nbs=p; =; REFERENCES =amTypeContainer = &=nbsp; ON DELETE =CASCADE &n=bsp; &nb=sp; ON UPDATE CASCADEgoALTER TABLE amContainer ADD CONSTRAINT =R_1 = FOREIGN KEY =(ParentContainerId) &n=bsp; &nb=sp; REFERENCES =amContainer &nbs=p; =; ON UPDATE CASCADEgoI'm getting an error on R_1 (that =last ALTER TABLE command):Server: Msg 1785, Level 16, State 1, =Line 3Introducing FOREIGN KEY constraint 'R_1' on table 'amContainer' =may cause cycles ormultiple cascade paths. Specify ON DELETE NO =ACTION or ON UPDATE NO ACTION, or modifyother FOREIGN KEY =constraints.Server: Msg 1750, Level 16, State 1, Line 3Could not create constraint. See =previous errors.Is this not possible with DRI? Do I need to =resort to triggers to apply what I want?Thanks for any help you can provide!John =Peterson

--=_NextPart_000_01ED_01C36198.39729640--|||This is a multi-part message in MIME format.
--=_NextPart_000_0294_01C3619F.7B4E4FD0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
In that script you posted, just don't run the R_9 and do run the R_8.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message =news:OtZgG6bYDHA.212@.TK2MSFTNGP12.phx.gbl...
Tom, you're exactly right. I wanted the amRights table to either have a =relationship from the amMember table (which as a FK from amObject) or =the amObject table directly. See the attached ERwin diagram. Any =suggestions for how I might specify the RI in this case?
Thanks again for your time and your help!
John Peterson
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:%23FDYInbYDHA.1940@.TK2MSFTNGP10.phx.gbl...
This type of problem is not limited to a self-referencing table. =Rather, it occurs any time there is a circular dependency. Already, =there R_9 establishes a relationship between amRights and amObject. R_5 =establishes the relationship between amObject and amMember. By adding =R_8 - between amRights and amMember - you have completed the circle.
The way to see this is to look at the ER diagram. It looks like you =should remove R_9 and install R_8.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message =news:uFC7lGbYDHA.2448@.TK2MSFTNGP09.phx.gbl...
Tom, I'm still having trouble with other aspects of DRI. Please see =the attached DDL -- this time it's with my R_8 relationship. I don't =quite "grok" why this is problematic. (It's not as clear-cut as a =self-referencing table.)
Any additional help you can provide would be greatly appreciated!
John Peterson
"John Peterson" <j0hnp@.comcast.net> wrote in message =news:%23BK4wDbYDHA.440@.tk2msftngp13.phx.gbl...
Thanks, Tom! Maybe I can "mix and match" and only use triggers =where necessary.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:OPg2l$aYDHA.1640@.TK2MSFTNGP10.phx.gbl...
You cannot use DRI for cascades on a self-referencing table. For =this, you will need a trigger.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message =news:etENJ7aYDHA.2572@.TK2MSFTNGP09.phx.gbl...
(SQL Server 2000, SP3)
Hello all!
I am trying to use ERwin to create a new schema. In the past, =because of limitations with
SQL Server's declarative referential integrity (DRI), I typically =did all my RI through
triggers. I thought I'd take the plunge and try using DRI with a
Parent-Cascade/Child-Restrict paradigm.
When I generate the script, I get the following:
CREATE TABLE amContainer (
ContainerId int IDENTITY(1, 1),
ParentContainerId int NULL,
TypeContainerId int NOT NULL,
CONSTRAINT XPKamContainer
PRIMARY KEY (ContainerId)
)
go
CREATE TABLE amTypeContainer (
TypeContainerId int IDENTITY(1, 1),
Name varchar(255) NOT NULL,
CONSTRAINT XPKamTypeContainer
PRIMARY KEY (TypeContainerId)
)
go
ALTER TABLE amContainer
ADD CONSTRAINT R_3
FOREIGN KEY (TypeContainerId)
REFERENCES amTypeContainer
ON DELETE CASCADE
ON UPDATE CASCADE
go
ALTER TABLE amContainer
ADD CONSTRAINT R_1
FOREIGN KEY (ParentContainerId)
REFERENCES amContainer
ON UPDATE CASCADE
go
I'm getting an error on R_1 (that last ALTER TABLE command):
Server: Msg 1785, Level 16, State 1, Line 3
Introducing FOREIGN KEY constraint 'R_1' on table 'amContainer' =may cause cycles or
multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE =NO ACTION, or modify
other FOREIGN KEY constraints.
Server: Msg 1750, Level 16, State 1, Line 3
Could not create constraint. See previous errors.
Is this not possible with DRI? Do I need to resort to triggers to =apply what I want?
Thanks for any help you can provide!
John Peterson
--=_NextPart_000_0294_01C3619F.7B4E4FD0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

In that script you posted, just don't =run the R_9 and do run the R_8.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"John Peterson" wrote in =message news:OtZgG6bYDHA.212@.T=K2MSFTNGP12.phx.gbl...
Tom, you're exactly right. I =wanted the amRights table to either have a relationship from the amMember table =(which as a FK from amObject) or the amObject table directly. See the attached =ERwin diagram. Any suggestions for how I might specify the RI in this case?
Thanks again for your time and your help!
John Peterson
"Tom Moreau" = wrote in message news:%23FDYInbYDHA.=1940@.TK2MSFTNGP10.phx.gbl...
This type of problem is not limited =to a self-referencing table. Rather, it occurs any time there is a =circular dependency. Already, there R_9 establishes a relationship =between amRights and amObject. R_5 establishes the relationship between =amObject and amMember. By adding R_8 - between amRights and amMember - =you have completed the circle.

The way to see this is to look at =the ER diagram. It looks like you should remove R_9 and install R_8.
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql

"John Peterson" wrote in =message news:uFC7lGbYDHA.2448=@.TK2MSFTNGP09.phx.gbl...
Tom, I'm still having trouble with =other aspects of DRI. Please see the attached DDL -- this time it's =with my R_8 relationship. I don't quite "grok" why this is =problematic. (It's not as clear-cut as a self-referencing table.)

Any additional help you can =provide would be greatly appreciated!

John Peterson

"John Peterson" wrote in =message news:%23BK4wDbYDHA.4=40@.tk2msftngp13.phx.gbl...
Thanks, Tom! Maybe I can ="mix and match" and only use triggers where necessary.
"Tom Moreau" = wrote in message news:OPg2l$aYDHA.1640=@.TK2MSFTNGP10.phx.gbl...
You cannot use DRI for cascades =on a self-referencing table. For this, you will need a trigger.
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, =SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql

"John Peterson" wrote =in message news:etENJ7aYDHA.2572=@.TK2MSFTNGP09.phx.gbl...(SQL Server 2000, SP3)Hello all!I am trying to use =ERwin to create a new schema. In the past, because of limitations =withSQL Server's declarative referential integrity (DRI), I typically did =all my RI throughtriggers. I thought I'd take the plunge and =try using DRI with aParent-Cascade/Child-Restrict paradigm.When =I generate the script, I get the following:CREATE TABLE =amContainer ( ContainerId =int IDENTITY(1, 1), ParentContainerId int NULL, TypeContainerId int NOT NULL, CONSTRAINT =XPKamContainer &=nbsp; PRIMARY KEY (ContainerId))goCREATE TABLE =amTypeContainer ( TypeContainerId int IDENTITY(1, 1), =Name &nb=sp; varchar(255) NOT NULL, =CONSTRAINT =XPKamTypeContainer &nb=sp; PRIMARY KEY (TypeContainerId))goALTER TABLE amContainer ADD CONSTRAINT = =R_3 = FOREIGN KEY =(TypeContainerId) &nbs=p; =; REFERENCES =amTypeContainer = &=nbsp; ON DELETE =CASCADE &n=bsp; &nb=sp; ON UPDATE CASCADEgoALTER TABLE amContainer ADD CONSTRAINT = =R_1 = FOREIGN KEY =(ParentContainerId) &n=bsp; &nb=sp; REFERENCES =amContainer &nbs=p; =; ON UPDATE CASCADEgoI'm getting an error on R_1 =(that last ALTER TABLE command):Server: Msg 1785, Level 16, State 1, =Line 3Introducing FOREIGN KEY constraint 'R_1' on table ='amContainer' may cause cycles ormultiple cascade paths. Specify ON DELETE NO =ACTION or ON UPDATE NO ACTION, or modifyother FOREIGN KEY constraints.Server: Msg 1750, Level 16, State 1, Line =3Could not create constraint. See previous errors.Is this not =possible with DRI? Do I need to resort to triggers to apply what I want?Thanks for any help you can provide!John =Peterson

--=_NextPart_000_0294_01C3619F.7B4E4FD0--

Friday, March 23, 2012

problem with adding sql:relation

problem with adding sql:relation

Monday, February 20, 2012

Problem using DRI with CASCADE action.

updating text datatype

Blog Archive

About Me