March 7, 2010 at 1:01 am
I've table and rows as follow,
CREATE TABLE [dbo].[tCout](
[idx] [smallint] IDENTITY(1,1) NOT NULL,
[cd] [varchar](20) NOT NULL,
[desn] [varchar](50) NOT NULL,
[inettrnx] [bit] NOT NULL,
[stat] [bit] NOT NULL,
[remk] [varchar](100) NOT NULL,
[crtby] [varchar](20) NOT NULL,
[crtdte] [smalldatetime] NOT NULL CONSTRAINT [DF_TCounter_crtdte] DEFAULT (getdate()),
[updby] [varchar](20) NOT NULL CONSTRAINT [DF_tCounter_updby] DEFAULT ('na'),
[upddte] [smalldatetime] NOT NULL CONSTRAINT [DF_tCounter_upddte] DEFAULT (getdate()),
[editno] [smallint] NOT NULL CONSTRAINT [DF_tCounter_editno] DEFAULT ((1)),
CONSTRAINT [PK_tCounter] PRIMARY KEY CLUSTERED
(
[idx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY],
CONSTRAINT [tcounter01] 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 tCout
(cd, desn, inettrnx, stat, remk, crtby)
values
('kl','kuala lumpur',1,1,'','admin');
insert into tCout
(cd, desn, inettrnx, stat, remk, crtby)
values
('iph','ipoh',1,1,'','admin');
My 2nd table as follow,
CREATE TABLE [dbo].[tH](
[idx] [smallint] IDENTITY(1,1) NOT NULL,
[tcoutcd] [varchar](20) NOT NULL,
CONSTRAINT [PK_tH] 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]
Below is my scenario
1. if me execute
insert into tH values('kl');
The record in tH is consider not rubbish, because the record exist in tCout(cd)
2. if me execute
insert into tH values('klx');
The record in tH is consider rubbish, because the record not exist in tCout(cd)
I knew how to using relationship to prevent a rubbish record.
I'm looking for help as follow,
1. To create a trigger on tH to prevent a rubbish record.
March 7, 2010 at 2:40 am
What is the reason not to use a foreign key?
Seems like the appropriate solution for the requirement.
March 7, 2010 at 3:03 am
lmu92 (3/7/2010)
What is the reason not to use a foreign key?Seems like the appropriate solution for the requirement.
Now, i'm drop tH and using foreign key as follow,
CREATE TABLE [dbo].[tH](
[idx] [smallint] IDENTITY(1,1) NOT NULL,
[tcoutcd] [varchar](20) NOT NULL,
CONSTRAINT [PK_tH] 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].[tH] WITH CHECK ADD CONSTRAINT [FK_tH_tcoutcd] FOREIGN KEY([tcoutcd])
REFERENCES [dbo].[tCout] ([cd])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tH] CHECK CONSTRAINT [FK_tH_tcoutcd]
2nd, im create another table as follow,
CREATE TABLE [dbo].[tD](
[idx] [smallint] IDENTITY(1,1) NOT NULL,
[thidx] [smallint] NOT NULL,
[tcoutcd] [varchar](20) NOT NULL,
CONSTRAINT [PK_tD] 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].[tD] WITH CHECK ADD CONSTRAINT [FK_tD_tcoutcd] FOREIGN KEY([tcoutcd])
REFERENCES [dbo].[tCout] ([cd])
GO
ALTER TABLE [dbo].[tD] CHECK CONSTRAINT [FK_tD_tcoutcd]
GO
ALTER TABLE [dbo].[tD] WITH CHECK ADD CONSTRAINT [FK_tD_thidx] FOREIGN KEY([thidx])
REFERENCES [dbo].[tH] ([idx])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tD] CHECK CONSTRAINT [FK_tD_thidx]
Current This tD using Delete No Action, and Update No Action.
I cannot set Delete Cascade and Update Cascade. The error as follow,
Unable to create relationship 'FK_tD_tcoutcd'.
Introducing FOREIGN KEY constraint 'FK_tD_tcoutcd' on table 'tD' 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
My problem is,
1. with Delete No Action and Update No Action, i cannot perform
update tcout set cd='iphx' where idx=1
if tD(tcoutcd) contains row refer to tcout(cd)
March 7, 2010 at 3:20 am
That's not really a normalized database design...
If you have a lookup table (like tH) you should reference the primary key (ID) of that table rather than the tcoutcd column you used.
If you need to use the values from tcoutcd then make this column your primary key of tH table.
Or use te values of tH.idx instead of tcoutcd as a reference in your tCout table.
March 7, 2010 at 8:28 am
ok sir. will take not on that
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply