Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2013
    Posts
    3

    Smile Problem with Duplicate Keys

    I have a table with following fields

    FIELDKEY int autoincrement
    FIELD1 varchar
    FIELD2 varchar
    FIELD3 varchar
    FIELD4 varchar

    When an insert is done twice with the values 1,2,3,4. The row for FIELD1...FIELD4 are populated with 1,2,3,4 twice. Is there a way to
    make these values four fields unique such that it would not allow duplicates
    for these four field combinations?

    Thanks.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about to add an unique consraint?

    Something like...
    ALTER TABLE <your table>
    ADD CONSTRAINT unique_field1_2_3_4 UNIQUE(FIELD1 , FIELD2 , FIELD3 , FIELD4)

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Or just add a unique index. When you add a unique constraint, DB2 will create a unique index for you if one does not already exist on those same columns.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Jan 2013
    Posts
    310
    Your approach to SQL is fundamentally wrong. This nameless narrative is not a table at all! This is a deck of punch cards. A table has a key, not a sequential position in a deck of punch cards. This narrative has no key; a key is never a physical hardware counter on one machine. The rest of this is also full of errors.

    1. You use VARCHAR(1) but show integer data in the narrative.
    2. You do not seem to know that fields and columns are totally different concepts. Where are any constraints? Etc?
    3. You do not seem to know what First Normal Form (1NF) means and what a “repeated group violation” is.
    4. You do not know ISO-11179 naming rules. Okay, that is a little advanced, but you will need it.

    Here is a start:

    CREATE TABLE Foobar
    (field1 INTEGER NOT NULL, -- better names needed!
    field2 INTEGER NOT NULL,
    field3 INTEGER NOT NULL,
    field4 INTEGER NOT NULL,
    PRIMARY KEY (field1, field2, field3, field4));

    Get a copy of MANGA GUIDE TO DATABASE to get you started. At this point, you are the “Flat Earth kid” in a Geography class.

  5. #5
    Join Date
    Nov 2011
    Posts
    319
    hi celko,
    Are you joe celko ? the author of the <<SQL for Smarties>>
    and the <<Thinking in sets>>?

  6. #6
    Join Date
    Jan 2013
    Posts
    310
    Yes.
    --CELKO--

    Books in Celko Series for Morgan-Kaufmann Publishing:

    Analytics and OLAP in SQL /
    Data and Databases: Concepts in Practice Data /
    Measurements and Standards in SQL SQL for Smarties /
    SQL Programming Style /
    SQL Puzzles and Answers /
    Thinking in Sets /
    Trees and Hierarchies in SQL

  7. #7
    Join Date
    Jan 2013
    Posts
    3
    Hi Joe,

    Thanks so much Sir. You are truly a genius. This makes me want to buy your books.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •