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.

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)

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.

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.

hi celko,
Are you joe celko ? the author of the <<SQL for Smarties>>
and the <<Thinking in sets>>?

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

Hi Joe,

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