SQL Unique Column Combination

Question

I was just wondering the best way to do this.

I have a database with 2 tables.

First Table

id - primary key
title

Second Table

id - primary key
first_id
order

I was wondering what is the best way to make sure if when inserting if the combination of first_id and order exists then update else insert?

eg if I try inserting a record into the Second Table first_id = 10 and order = 1 and that already exists then update instead of insert a new record. I read somewhere about making a combination of columns unique but wasn't sure how to.

EDIT: Sorry I forgot to mention that im using MySQL

I could give you another more efficient solution if you hook us with your database platform. The quickest is to perform the update check how many rows affected if it is 0 then do an insert. The syntax varies against the big 3 and SQL Server has an even more efficient way. — JStead, May 21 '12 at 12:36
Using MySQL - I did see this http://stackoverflow.com/questions/707767/sql-unique-record-not-column Can you set that to update as well? — hooligan, May 21 '12 at 12:39

score 2 · Answer 1 · answered May 21 '12 at 12:28

2

assuming sql -server...

if exists ( select 1 from table2 where  id=@id and firstId=@firstid)
begin
  update table2 set(...)
end
begin
insert into table2 select ...
end

please make sure that first_id is foreign key to table1's id.

answered May 21 '12 at 12:28

Royi Namir

144,742
138
468
792

by assuming sql -server. Do you mean MySQL? – hooligan May 22 '12 at 11:04
@hooligan nop. i mean MicrosoftSqlServer – Royi Namir May 22 '12 at 11:23

score 0 · Answer 2 · answered May 21 '12 at 12:33

For MSSQL, you could use something like this:

IF NOT EXISTS (SELECT id from Second Table WHERE first_id = 10 AND order = 1)
BEGIN
   INSERT INTO Second Table (first_id, order) VALUES (10, 1)
END
ELSE
BEGIN
   UPDATE Second Table SET order = 1 WHERE first_id = 10
END

As for making it unique, that just ensures that you cannot insert those values twice, but i think it's probably better to check this before tryint to insert. (A combination of both would be perfect, I guess)

You can make the combination of first_id and order UNIQUE to prevent it.

score 0 · Answer 3 · edited May 23 '17 at 12:27

If you are inserting the data directly in the table you can make a Trigger something like the answer of Royi will help you.

If you want the insert fails when you try to insert a pair of data, or in other words if you want to make a Unique constraint maybe this link will help you.

Other way to do that is insert the data via a stored procedure and perform the checks before insert.

-Sadd

score 0 · Answer 4 · answered May 21 '12 at 12:40

0

This way in an update scenario you only scan the table once. Still the same performance on inserts.

UPDATE Second Table SET order = 1 WHERE first_id = 10
if ROW_COUNT() = 0 
begin
   INSERT INTO Second Table (first_id, order) VALUES (10, 1)
end

answered May 21 '12 at 12:40

JStead

1,710
11
12

SQL Unique Column Combination

4 Answers4