If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Data Access, Manipulation & Batch Languages > ANSI SQL > Combined columns in table to be unique

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Feb 2013
Posts: 2
Combined columns in table to be unique

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?
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Nov 2003
Posts: 2,806
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.
__________________
I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

Tips for good questions:

http://tkyte.blogspot.de/2005/06/how...questions.html
http://wiki.postgresql.org/wiki/SlowQueryQuestions
http://catb.org/esr/faqs/smart-questions.html
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Feb 2013
Posts: 2
Thanks, the CREATE UNIQUE INDEX worked fine. I'm using SQLite.

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

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On