sql unique and setting constraints on inserts

Question

I have a database where I need to avoid inserting duplicates. The requirements are:

For the subset of rows with matching column 1, there can not be any that have the same column 2.

For the subset of rows with matching column 1, there can not be any that have the same column 3 and 4.

I'm new to SQL so is there a way of setting these relationships when I create the database (create table) or do I have to do a select and do these checks manually before inserting into the table?

Answer 1

Just to get the ball rolling ...

Could you go back immediately after the insert and delete duplicated rows based on the contraints you mentioned?

Answer 2

In effect, you need the column 1 and 2 to be unique, and also columns 1,3 and 4 to be unique. So when you create the table, you can use two UNIQUE constaints:

CREATE TABLE tbl (
  col1 varchar(255),
  col2 varchar(255),
  col3 varchar(255),
  col4 varchar(255),
  CONSTRAINT uc_first UNIQUE(col1, col2),
  CONSTRAINT uc_second UNIQUE(col1, col3, col4)
)