SQL Unique Record not column?

Question

Is there a way to insert into an SQL database where the whole record is unique? I know you can make primary keys and unique columns, but that is not what I want.

What is the best way of doing this without overloading the database? I have seen a sort of subquery where you use "WHERE NOT EXISTS ()"

I just want to know the most efficient way of inserting a record so that there are no duplicates of the EXACT same record.

Thank you in advance!

Answer 1

You could add a unique constraint to the table definition and include all columns (except the primary key, I believe). Unique constraints create indexes, so I have no idea what sort of performance impact this might have, but I'd guess the fewer columns, the better.

This will add such a constraint to an existing table:

ALTER TABLE SampleTable
ADD CONSTRAINT [uc_UniqueRow] UNIQUE (Column1, Column2, Column3)

Note that there are certain restrictions on column types etc. so this may or may not work for your table. See Books Online for details: http://msdn.microsoft.com/en-us/library/ms177420(sql.90).aspx

Answer 2

You know how to make a unique index on a column.

Just make that on all the columns that should, together, be unique:

create unique index foo 
on tablename(columnone, columntwo, columnthree);

Note that the need to do this may be an indication that your table is not sufficiently normalized.

Answer 3

You could do:

create table #temp (val1 varchar(30), val2 varchar(30))


declare @val1 varchar(30),@val2 varchar(30)
select @val1='josh',@val2='bob'

insert into #temp
select @val1, @val2
where not exists (select * from #temp where val1=@val1 and val2=@val2)

Run the bottom half twice and it won't insert the record more then once.

Answer 4

Your question was pretty clear (not sure why others answered with something you'd specifically wanted to exclude), and something I'd spent over an hour trying to find ...

This link was most helpful http://www.timrosenblatt.com/blog/2008/03/21/insert-where-not-exists/

On mySQL 5.1.35 ...

    mysql> CREATE TABLE testDupeSyntax  (name varchar(10), place varchar(20) );
Query OK, 0 rows affected (0.13 sec)

mysql>
mysql> INSERT INTO testDupeSyntax SELECT 'mango1', 'mango2' FROM DUAL WHERE NOT
EXISTS( SELECT name FROM testDupeSyntax WHERE name='mango1' LIMIT 1 );
Query OK, 1 row affected (0.06 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM testDupeSyntax;
+--------+--------+
| name   | place  |
+--------+--------+
| mango1 | mango2 |
+--------+--------+
1 row in set (0.00 sec)

mysql> INSERT INTO testDupeSyntax SELECT 'mango1', 'mango2' FROM DUAL WHERE NOT
EXISTS( SELECT name FROM testDupeSyntax WHERE name='mango1' LIMIT 1 );
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM testDupeSyntax;
+--------+--------+
| name   | place  |
+--------+--------+
| mango1 | mango2 |
+--------+--------+
1 row in set (0.00 sec)

mysql> INSERT INTO testDupeSyntax SELECT 'mango1', 'mango2' FROM DUAL WHERE NOT
EXISTS( SELECT name FROM testDupeSyntax WHERE name='mango1' AND place='mango2' LIMIT 1 );
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM testDupeSyntax;
+--------+--------+
| name   | place  |
+--------+--------+
| mango1 | mango2 |
+--------+--------+
1 row in set (0.00 sec)

mysql> INSERT INTO testDupeSyntax SELECT 'mango1', 'mango2' FROM DUAL WHERE NOT
EXISTS( SELECT name FROM testDupeSyntax WHERE name='mango1' AND place='mango3' LIMIT 1 );
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM testDupeSyntax;
+--------+--------+
| name   | place  |
+--------+--------+
| mango1 | mango2 |
| mango1 | mango2 |
+--------+--------+
2 rows in set (0.00 sec)

mysql>