Results 1 to 3 of 3
  1. #1
    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?

  2. #2
    Join Date
    Nov 2003
    Posts
    2,821
    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

  3. #3
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •