If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > Problem with Duplicate Keys

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Feb 2008
Location: Japan
Posts: 3,419
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)
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: May 2003
Location: USA
Posts: 5,734
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
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Jan 2013
Posts: 298
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.
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Nov 2011
Posts: 314
hi celko,
Are you joe celko ? the author of the <<SQL for Smarties>>
and the <<Thinking in sets>>?
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Jan 2013
Posts: 298
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
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
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.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On