I'm using v7.3.2 from OpenBSD 3.4 ports collection. I'm looking for a way
to enforce unique rows in a table with about 20 columns. I found a way to
do it, but it looks a little clunky so I was wondering if there was a better
way.
What I found is I have to do a:
CREATE TABLE tblname (col1 text, col2 text, col3 text, UNIQUE(col1, col2,
col3) );
In order for this command to work on a table with more columns, I have to
include all column names individually within the UNIQUE() option. I would
think I could say something more simple like:
CREATE TABLE tblname (col1 text, col2 text, col3 text, UNIQUE(*) );
It just seemed natural to me to allow the use of a wildcard anywhere within
a sql statement, but apparently that's not the case. Is there a reason the
command can't be used in this fashion or maybe is there a better way to do
it using another command?
FYI the reason why I need uniqueness for entire rows is because sometimes
when testing I've added the same data into the table more than once, and I'd
like to prevent that. As long as I use the distinct option in select
statements it hasn't been a problem, but enforcing unique rows is a more
elegant solution. Also, the table in question currently has about 20
columns by 300,000 rows, just in case there might be any problems with
keeping integrity on that many rows.
Matt
_________________________________________________________________
Best Restaurant Giveaway Ever! Vote for your favorites for a chance to win
$1 million! http://local.msn.com/special/giveaway.asp