Combined columns in table to be unique

FrOzeN89 · #1 (**permalink**) 02-10-13, 01:03

Hi there,

I have a database I'm building similar to:

Code:

CREATE TABLE Products (
  Products_Id   integer PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
  ItemNumber    integer NOT NULL UNIQUE,
  Description   nvarchar(255) NOT NULL
);

CREATE TABLE Sales (
  Sales_Id      integer PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
  ItemNumber    integer NOT NULL,
  TheDate       datetime NOT NULL,
  Volume        real NOT NULL,
  /* Foreign key */
  FOREIGN KEY (ItemNumber)
    REFERENCES Products(ItemNumber)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
);

In the Sales table I would like to create a constraint to limit (ItemNumber + TheDate) to be UNIQUE when combined, but can be duplicated providing either value is different.

Also, more importantly, is a constraint like this going to slow data entry down? I intend of having up to 2-3 million rows within this table. Approximately 5,000 entries per day adds almost 2 million rows per year. Would it be better to have more thorough code to prevent entering duplicates?

shammat · #2 (**permalink**) 02-10-13, 03:30

First: specifying unique and primary key is unnecessary in the table definition. A primary key is unique by definition.

Quote:

Originally Posted by FrOzeN89

in the Sales table I would like to create a constraint to limit (ItemNumber + TheDate) to be UNIQUE when combined

Create a unique index (or constraint):

Code:

create uniqe index unique_itemnumber_date on sales (ItemNumber,TheDate);

or

Code:

alter table sales add constraint 
     unique_itemnumber_date unique ((ItemNumber,TheDate);

Logically there is no difference between these two versions.
Some DBMS only allow a unique constraint to be the target of a foreign key (but not a unique index).

Quote:

Originally Posted by FrOzeN89

Also, more importantly, is a constraint like this going to slow data entry down? I intend of having up to 2-3 million rows within this table.

There is a slight performance hit when creating an index, but that outweighs by far the advantages of having valid data in your datababase.

Quote:

Originally Posted by FrOzeN89

Would it be better to have more thorough code to prevent entering duplicates?

No, absolutely not. The only place to ensure these kind of constraints is in the database.

Btw: you have used the AUTOINCREMENT which is not standard SQL. It might be better to ask this question in the forum of your specific DBMS.

FrOzeN89 · #3 (**permalink**) 02-10-13, 05:58

Thanks, the CREATE UNIQUE INDEX worked fine. I'm using SQLite.

I also removed the extra UNIQUE constraints from the PRIMARY KEY's.

Internet Services

Web Development

Digital Marketing

Consumer Tech