Path: utzoo!utgpu!news-server.csri.toronto.edu!rpi!zaphod.mps.ohio-state.edu!mips!spool.mu.edu!uwm.edu!ogicse!sequent!muncher.sequent.com!sweiger
From: sweiger@sequent.com
Newsgroups: comp.databases
Subject: Re: SQL Duplicate Row Deletion ???
Message-ID: <1991Apr3.201506.14255@sequent.com>
Date: 3 Apr 91 20:15:06 GMT
References: <91091.141528SYSPMZT@GECRDVM1.BITNET> <1991Apr3.010838.2063@eng.ufl.edu>
Sender: news@sequent.com (News on Muncher)
Organization: Sequent Computer Systems, Inc.
Lines: 24

In article <1991Apr3.010838.2063@eng.ufl.edu> tsao@helios.tcad.ee.ufl.edu writes:
>I am now using Ingres, I am surprised that it allows the user to append 
>duplicated rows (including a null row) into a table unless you define a key
>using create unique index.
>
>Would anybody give me an example justifying the need for duplicated tuples
>in a table ?
>
> 
SQL specifically allows duplicate keys and nulls as attributes.  The only
way to force unique primary keys in SQL is to CREATE UNIQUE INDEX using 
that key, a construct that almost every SQL dialect supports.  In theory,
relational databases are not supposed to contain duplicate keys.  In
theory, there is always a primary key which identifies the tuple does
not contain null attributes.  SQL deviates from relational model in this 
regard, and many words of criticism have been written about this deviation 
from the model.  It certainly does complicate RDBMS algorithms.  And joins
can produce some surprising results.  But my personal opinion is this:  
A wise database designer makes sure that his database has a primary key.  
But the fact that SQL allows duplicates recognizes some legitimate, real 
world situations where it just isn't feasible, or perhaps desirable in 
a performance sense, to create a primary key for every tuple.

--Mark