March 5, 2010 at 8:58 am
I've master tables and row as follow,
CREATE TABLE [dbo].[tCompy](
[idx] [smallint] IDENTITY(1,1) NOT NULL,
[cd] [varchar](50) NOT NULL,
[desn] [varchar](50) NOT NULL,
[stat] [bit] NOT NULL,
[crtby] [varchar](20) NOT NULL,
[crtdte] [smalldatetime] NOT NULL CONSTRAINT [DF_tCompany_crtdte] DEFAULT (getdate()),
[updby] [varchar](20) NOT NULL CONSTRAINT [DF_tCompany_updby] DEFAULT ('na'),
[upddte] [smalldatetime] NOT NULL CONSTRAINT [DF_tCompany_upddte] DEFAULT (getdate()),
[editno] [smallint] NOT NULL CONSTRAINT [DF_tCompany_editno] DEFAULT ((1)),
CONSTRAINT [PK_tCompany] 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 [tcompany01] 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 tCompy(cd,desn,stat,crtby)
values('snt','sn tech',1,'admin');
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');
Let's say, i design table as follow,
CREATE TABLE [dbo].[t1](
[idx] [smallint] IDENTITY(1,1) NOT NULL,
[tcompycd] [varchar](50) 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]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[t1] WITH CHECK ADD CONSTRAINT [FK_t1_tcompycd] FOREIGN KEY([tcompycd])
REFERENCES [dbo].[tCompy] ([cd])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[t1] CHECK CONSTRAINT [FK_t1_tcompycd];
CREATE TABLE [dbo].[t2](
[idx] [smallint] IDENTITY(1,1) NOT NULL,
[t1idx] [smallint] NOT NULL,
[tcoutcd] [varchar](20) NOT NULL,
CONSTRAINT [PK_t2] 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].[t2] WITH CHECK ADD CONSTRAINT [FK_t2_t1idx] FOREIGN KEY([t1idx])
REFERENCES [dbo].[t1] ([idx])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[t2] CHECK CONSTRAINT [FK_t2_t1idx]
GO
ALTER TABLE [dbo].[t2] WITH CHECK ADD CONSTRAINT [FK_t2_tcoutcd] FOREIGN KEY([tcoutcd])
REFERENCES [dbo].[tCout] ([cd])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[t2] CHECK CONSTRAINT [FK_t2_tcoutcd];
CREATE TABLE [dbo].[t3](
[idx] [smallint] IDENTITY(1,1) NOT NULL,
[t1idx] [smallint] NOT NULL,
[tcoutcd] [varchar](20) NOT NULL,
CONSTRAINT [PK_t3] 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].[t3] WITH CHECK ADD CONSTRAINT [FK_t3_t1idx] FOREIGN KEY([t1idx])
REFERENCES [dbo].[t1] ([idx])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[t3] CHECK CONSTRAINT [FK_t3_t1idx]
GO
ALTER TABLE [dbo].[t3] WITH CHECK ADD CONSTRAINT [FK_t3_tcoutcd] FOREIGN KEY([tcoutcd])
REFERENCES [dbo].[tCout] ([cd])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[t3] CHECK CONSTRAINT [FK_t3_tcoutcd]
Me execute below insert statement into t1 and t2 as follow,
declare @idx smallint
insert into t1 values('snt');
set @idx=scope_identity();
insert into t2(t1idx,tcoutcd) values(@idx,'iph');
My question is,
1. I knew to create unique contraint in 1 table
2. It's possible to create unique constraint into 2 table?
3. If possible, how to make it combination of t1(tcompycd) and t2(tcoutcd) is a unique?
4. If not possible, what the option to prevent below statement
declare @idx smallint
insert into t1 values('snt');
set @idx=scope_identity();
insert into t2(t1idx,tcoutcd) values(@idx,'iph');
inserted into t1 and t2
Really Need help. 🙁
March 8, 2010 at 7:00 am
I think I understand the question.
2. Yes, it's certainly possible to add a unique constraint to table 2. In fact, you should. Too many people rely on the ID column as making the table unique, but as you're pointing out, you can create as many entries with different values as you want.
3. The simplest method would be to put a unique index on the FK from table 1 and the value from table 2 that define the unique instance like this:
CREATE UNIQUE NONCLUSTERED INDEX [MyUniqueIndex]
ON dbo.t2 (t1idx,tcoutcd)
An unique index and a unique constraint are the same thing (in fact, creating a unique constraint creates a unique index).
4. Nothing unless you put that unique constraint in place.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 9, 2010 at 11:04 pm
Am I right in understanding that you want to allow a key value to appear in one of two tables, but not in both? This is sometimes called a "distributed key". See:
March 10, 2010 at 12:53 am
Grant Fritchey (3/8/2010)
An unique index and a unique constraint are the same thing (in fact, creating a unique constraint creates a unique index).
Except for 2 points
Unique index can have include columns, unique constrain can't
Unique index can be filtered (SQL 2008) unique constrain can't.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 10, 2010 at 2:12 am
GilaMonster (3/10/2010)
Grant Fritchey (3/8/2010)
An unique index and a unique constraint are the same thing (in fact, creating a unique constraint creates a unique index).Except for 2 points
Unique index can have include columns, unique constraint can't
Unique index can be filtered (SQL 2008) unique constraint can't.
There are a number of other differences, one that leaps to mind is that a unique index can be modified whereas a constraint must be dropped and re-created.
The other differences are in a similar vein: constraints tend to have fewer syntactical options.
A unique constraint is different from a unique index - but it is true to say that a unique constraint is enforced by a unique index.
BTW my favourite use for a filtered unique index is one that allows any number of NULLs but enforces uniqueness on non-NULL values. Cool.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 10, 2010 at 6:01 am
Paul White (3/10/2010)
GilaMonster (3/10/2010)
Grant Fritchey (3/8/2010)
An unique index and a unique constraint are the same thing (in fact, creating a unique constraint creates a unique index).Except for 2 points
Unique index can have include columns, unique constraint can't
Unique index can be filtered (SQL 2008) unique constraint can't.
There are a number of other differences, one that leaps to mind is that a unique index can be modified whereas a constraint must be dropped and re-created.
The other differences are in a similar vein: constraints tend to have fewer syntactical options.
A unique constraint is different from a unique index - but it is true to say that a unique constraint is enforced by a unique index.
BTW my favourite use for a filtered unique index is one that allows any number of NULLs but enforces uniqueness on non-NULL values. Cool.
Paul
All true, from both. I actually thought I had typed "effectively the same" but I left out a word. Sorry.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply