Geek Answers Handbook

Violation of PRIMARY KEY "PK_Address" restriction. etc .... what am I doing wrong?

The evening is all. I am new to SQL, but cheated quite a bit. I follow the manual that I found on the Internet to find out SQLafter 21 days, and I am having trouble figuring out what causes the error.

I am trying to use data INSERTin an existing table. The primary key for this table AddressID. The data I'm trying to enter is in the code below:

INSERT INTO [dbo].[Address]
       (AddressID,Street,City,State,ZipCode)
VALUES
       (1,'2400 Broadway','New York','NY',11201),
       (2,'320 21st Street','Atlanta','GA',303),
       (3,'439 Skyline Blvd','Seattle','WA',98101),
       (4,'56 Park Avenue','Dallas','TX',75201);
GO

I keep getting this error:

Msg 2627, Level 14, State 1, Line 2 Primary Key Violation Restriction "PK_Address". Unable to insert duplicate key in object 'Dbo.Address. The duplicate key value is (1).

, , . INSERT INTO , VALUES . , INSERT . 4 .

?

+4
4

, , . , .

INSERT INTO [dbo].[Address]
   (Street,City,State,ZipCode)
VALUES
   ('2400 Broadway','New York','NY',11201),
   ('320 21st Street','Atlanta','GA',303),
   ('439 Skyline Blvd','Seattle','WA',98101),
   ('56 Park Avenue','Dallas','TX',75201);
GO
+3

, AddressID = 1 , primary key on AddressID, .

INSERT INTO [dbo].[Address]
   (Street,City,State,ZipCode)
SELECT Street,City,State,ZipCode 
FROM  ( VALUES (1,'2400 Broadway','New York','NY',11201), 
               (2,'320 21st Street','Atlanta','GA',303), 
               (3,'439 Skyline Blvd','Seattle','WA',98101), 
               (4,'56 Park Avenue','Dallas','TX',75201)) 
v(addressid, street, city, state, zipcode) 
WHERE  NOT EXISTS (SELECT 1 
                   FROM   [dbo].[address] A 
                   WHERE  a.addressid = v.addressid)

AddressID identity, AddressID insert select.

0

. AddressID , , AddressID. , .

. , , AddressID 1. , AddressID IDENTITY . AddressID. - :

INSERT INTO [dbo].[Address]
       (Street,City,State,ZipCode)
VALUES
       ('2400 Broadway','New York','NY',11201),
       ('320 21st Street','Atlanta','GA',303),
       ('439 Skyline Blvd','Seattle','WA',98101),
       ('56 Park Avenue','Dallas','TX',75201)
       ('56 Park Avenue','Dallas','TX',75201);
 GO
0

:

  • AddressId (1) Address. . primary key.

, , , :

  • update insert . AddressId 1 AddressIds.

You better allow a Sql Serverdecision about your primary key value by creating an "identity column". You can create an identifier column when creating a table as follows:

CREATE TABLE [dbo].[Address]
(
 [AddressId] [int] IDENTITY(1,1) NOT NULL,
 [OtherColumns] [int] NULL,
 CONSTRAINT [PK_Address] PRIMARY KEY CLUSTERED  (AddressId ASC)
 )

Please note that if you created a table with Idenity column, you do not need to specify any values ​​during the operation insert, because Sql Server implicitly creates and inserts these values ​​for you.

0
source

More articles:


All Articles