Professional Web Applications Themes

do I have to have a PK/identity? - Microsoft SQL / MS SQL Server

I have 2 tables - Products and Categories I also have a ProductsCategories table that lists certain product(s) in various categories. for example: Product_id Category_id 1 5 1 6 2 5 3 7 4 7 Do I need to have a PK/identity for this table? i.e - ProductsCategories_id ? I'm not going to use that, and I was wondering if a clustered index on a different column would help me. Thanks!...

  1. #1

    Default do I have to have a PK/identity?

    I have 2 tables - Products and Categories
    I also have a ProductsCategories table that lists certain product(s) in
    various categories.

    for example:

    Product_id Category_id
    1 5
    1 6
    2 5
    3 7
    4 7

    Do I need to have a PK/identity for this table? i.e - ProductsCategories_id
    ? I'm not going to use that, and I was wondering if a clustered index on a
    different column would help me.

    Thanks!


    Jason Guest

  2. #2

    Default Re: do I have to have a PK/identity?

    You need a Primary Key (as does every table). You don't need IDENTITY:

    CREATE TABLE ProductCategories (product_id INTEGER NOT NULL REFERENCES
    Products (product_id), category_id INTEGER NOT NULL REFERENCES Categories
    (category_id), PRIMARY KEY (product_id, category_id))

    A PK is created as clustered unless you specify non-clustered.

    If this table has only these two columns then it makes sense to use a
    clustered index. I suggest that (product_id, category_id) is the best order
    for the columns in the index, assuming product_id is more selective than
    category_id.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --


    David Guest

  3. #3

    Default Re: do I have to have a PK/identity?

    How can I create dual columns PK through enterprise manager design dialog?

    Thanks!!!

    "David Portas" <org> wrote in message
    news:%phx.gbl... 
    primary [/ref]
    index [/ref]
    > Categories 
    > > order [/ref][/ref]
    than 
    > >
    > >[/ref]
    >
    >[/ref]


    Jason Guest

  4. #4

    Default Re: do I have to have a PK/identity?

    Sorry, just found it - simply highlight both columns with CTRL, right click,
    and check PK

    "David Portas" <org> wrote in message
    news:%phx.gbl... 
    primary [/ref]
    index [/ref]
    > Categories 
    > > order [/ref][/ref]
    than 
    > >
    > >[/ref]
    >
    >[/ref]


    Jason Guest

Similar Threads

  1. Replies: 2
    Last Post: March 18th, 03:39 AM
  2. Replies: 6
    Last Post: April 16th, 10:37 PM
  3. Replies: 5
    Last Post: November 8th, 05:25 PM
  4. IDENTITY is 0 ?
    By A Ratcliffe in forum ASP Database
    Replies: 6
    Last Post: October 22nd, 12:26 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139