June 14, 2016 at 2:30 pm
Hello,
I have this table structure:
I have primary keys on [V] and [CA] but I am able to insert duplicate values.
CREATE TABLE [dbo].[Test_A](
[V] [varchar](12) NOT NULL,
[CA] [varchar](2) NOT NULL,
[CE] [real] NULL,
[CL] [real] NULL,
[CX] [real] NULL,
PRIMARY KEY CLUSTERED
(
[V] ASC,
[CA] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
June 14, 2016 at 2:39 pm
we need an example of duplicates making it in.
when i test, i get an error violating the PK, which would be expected.
insert into [Test_A]([V],[CA])SELECT '1','2'
insert into [Test_A]([V],[CA]) SELECT '1','2'
Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK__Test_A__18F1501D36F51218'. Cannot insert duplicate key in object 'dbo.Test_A'. The duplicate key value is (1, 2).
Lowell
June 14, 2016 at 2:48 pm
Here's the one that I test out:
CREATE TABLE [dbo].[C](
[V] [varchar](12) NOT NULL,
[CT] [varchar](2) NOT NULL,
[CE] [real] NULL,
[CL] [real] NULL,
[CX1] [real] NULL,
CONSTRAINT [C$C2] PRIMARY KEY CLUSTERED
(
[V] ASC,
[CT] 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].[C] WITH NOCHECK ADD CONSTRAINT [C$CT$disallow_zero_length] CHECK ((len([CT])>(0)))
GO
ALTER TABLE [dbo].[C] CHECK CONSTRAINT [C$CT$disallow_zero_length]
GO
ALTER TABLE [dbo].[C] WITH NOCHECK ADD CONSTRAINT [C$V$disallow_zero_length] CHECK ((len([V])>(0)))
GO
ALTER TABLE [dbo].[C] CHECK CONSTRAINT [C$V$disallow_zero_length]
GO
June 14, 2016 at 2:59 pm
And example of duplicates that succeed? Because
INSERT INTO C (V, CT)
VALUES ('1234567890', 'aa')
GO
INSERT INTO C (V, CT)
VALUES ('1234567890', 'aa')
GO
Msg 2627, Level 14, State 1, Line 5
Violation of PRIMARY KEY constraint 'C$C2'. Cannot insert duplicate key in object 'dbo.C'. The duplicate key value is (1234567890, aa).
The statement has been terminated.
And by the way, you don't need to set ANSI PADDING off for the constraints, just use DATALENGTH function instead of LEN.
DATALENGTH(' ') = 1
DATALENGTH('') = 0
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
June 14, 2016 at 3:13 pm
Thanks for the prompt response.
So, if I try to insert the below , it executes:
Is it because it is composite key that col V is able to have duplicate values?
INSERT INTO C (V, CT)
VALUES ('1234567890', 'aa')
GO
INSERT INTO C (V, CT)
VALUES ('1234567890', 'ab')
GO
June 14, 2016 at 3:38 pm
Yes, that will insert successfully, and that's the correct, expected behaviour.
You've defined the primary key on two columns, therefore the combination of the two has to be unique, not the individual columns. If you want V to be unique, then either define the PK on V alone or add a unique constraint on V alone.
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
June 14, 2016 at 3:42 pm
Thank you for clarifying so that is called composite key right?
June 14, 2016 at 3:46 pm
You can call it that, yes.
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
June 14, 2016 at 3:48 pm
Thank you appreciate the help.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply