Need help on table design - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Need help on table design

Author

Topic

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-03-07 : 02:03:07

My 1st table and row as follow,

CREATE TABLE [dbo].[t1](
	[idx] [smallint] IDENTITY(1,1) NOT NULL,
	[cd] [varchar](10) NOT NULL,
	[desn] [varchar](100) NOT NULL,
 CONSTRAINT [PK_t1] PRIMARY KEY CLUSTERED 
(
	[idx] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [t101] UNIQUE NONCLUSTERED 
(
	[cd] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY];
insert into t1 values('iph','ipoh');
insert into t1 values('kntn','kuantan');

My 2nd table and row as follow,

CREATE TABLE [dbo].[t2H](
	[idx] [smallint] IDENTITY(1,1) NOT NULL,
	[tcompycd] [varchar](50) NOT NULL,
	[t1cd] [varchar](10) NOT NULL,
 CONSTRAINT [PK_t2H] PRIMARY KEY CLUSTERED 
(
	[idx] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [t2H01] UNIQUE NONCLUSTERED 
(
	[tcompycd] ASC,
	[t1cd] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[t2H]  WITH CHECK ADD  CONSTRAINT [FK_t2H_t1cd] FOREIGN KEY([t1cd])
REFERENCES [dbo].[t1] ([cd])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[t2H] CHECK CONSTRAINT [FK_t2H_t1cd];
insert into t2H values('psb','iph');

My 3rd table ad follow,

CREATE TABLE [dbo].[t2D](
	[idx] [smallint] IDENTITY(1,1) NOT NULL,
	[t2hidx] [smallint] NOT NULL,
	[t1cd] [varchar](10) NOT NULL,
 CONSTRAINT [PK_t2D] PRIMARY KEY CLUSTERED 
(
	[idx] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[t2D]  WITH CHECK ADD  CONSTRAINT [FK_t2D_t1cd] FOREIGN KEY([t1cd])
REFERENCES [dbo].[t1] ([cd])
GO
ALTER TABLE [dbo].[t2D] CHECK CONSTRAINT [FK_t2D_t1cd]
GO
ALTER TABLE [dbo].[t2D]  WITH CHECK ADD  CONSTRAINT [FK_t2D_t2hidx] FOREIGN KEY([t2hidx])
REFERENCES [dbo].[t2H] ([idx])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[t2D] CHECK CONSTRAINT [FK_t2D_t2hidx];
insert into t2D values(1,'kntn');

My problem is,
1. Me cannot set Delete Cascade, and Update Cascade on

ALTER TABLE [dbo].[t2D]  WITH CHECK ADD  CONSTRAINT [FK_t2D_t1cd] FOREIGN KEY([t1cd])
REFERENCES [dbo].[t1] ([cd])
GO
ALTER TABLE [dbo].[t2D] CHECK CONSTRAINT [FK_t2D_t1cd]

.
The error said,
Unable to create relationship 'FK_t2D_t1cd'.
Introducing FOREIGN KEY constraint 'FK_t2D_t1cd' on table 't2D' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Could not create constraint

As a result, i'm set as Delete No Action, and Update No Action

2. Once, i'm set Delete No Action, and Update No Action, i cannot perform below update statement

update t1 set cd='kntnx'
where idx=2;

The error was,
The UPDATE statement conflicted with the REFERENCE constraint "FK_t2D_t1cd". The conflict occurred in database "Cleanup", table "dbo.t2D", column 't1cd'.
My objective is,
1. When me perform

update t1 set cd='kntnx'
where idx=2;

t2H(t1cd) and t2D(t1cd) will update automatically.

What should i do to achieve my objective?
Really need help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources