Same elements in SQL table

Большаков Георгий 0 Reputation points
2023-02-02T11:22:05.1666667+00:00

I try to do this in SQL:

CREATE TABLE numbers (

id INTEGER PRIMARY KEY

);

INSERT INTO numbers VALUES (1);

INSERT INTO numbers VALUES (1);

INSERT INTO numbers VALUES (2);

INSERT INTO numbers VALUES (4);

SELECT * FROM numbers;

In respons, compiler writes "UNIQUE constraint failed" and prints "1 2 4". How can I print all values without error?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,323 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Olaf Helper 39,181 Reputation points
    2023-02-02T11:30:36.2466667+00:00

    Column "Id" is define as primary key and a PK can contain only unique values; you can not insert duplicates. You have to check if a value already exists.

    0 comments No comments

  2. Bjoern Peters 8,776 Reputation points
    2023-02-02T12:13:01.9866667+00:00

    Hi

    that won't happen.

    By default the PRIMARY KEY is always a UNIQUE key, so that is why you are getting the error "Unique constraint"... a row with value "1" is already existing.

    And if you just insert the values 1,2,4 then the resultset is correct ;-)

    And if you only execute the SELECT statement, you won't receive any error... the error comes from the insert statements.

    I hope my answer is helpful to you,

    Your

    Bjoern Peters

    If the reply was helpful, please upvote and/or accept it as an answer, as this helps others in the community with similar questions. Thanks!

    0 comments No comments

  3. CosmogHong-MSFT 20,976 Reputation points Microsoft Vendor
    2023-02-03T05:40:19.61+00:00

    Hi @Большаков Георгий

    As experts answered above, you cannot insert duplicate records into a column with Primary key constraint.

    User's image

    If you want to display 1,1,2,4 you could delete the Primary key in the code of Create table.

    Best regards,

    Cosmog Hong


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments