Professional Web Applications Themes

Autoincremented id with two primary keys - MySQL

Hi all. I have this table: CREATE TABLE sites_pages ( id int(6) NOT NULL, site_id int(4) NOT NULL, name varchar(80) NOT NULL, UNIQUE KEY site_id_name (site_id, name), PRIMARY KEY (id, site_id) ) TYPE=InnoDB; I would like to have an auto generated id but for each site. For example: id site_id name 1 1 index 2 1 home 3 1 article 1 2 index 2 2 home 3 2 article Is it possible to have this done automatically by mysql or I have to lock the table and do a thing like that: LOCK TABLE sites_pages; SELECT MAX(id) AS page_id FROM ...

  1. #1

    Default Autoincremented id with two primary keys

    Hi all. I have this table:

    CREATE TABLE sites_pages (
    id int(6) NOT NULL,
    site_id int(4) NOT NULL,
    name varchar(80) NOT NULL,
    UNIQUE KEY site_id_name (site_id, name),
    PRIMARY KEY (id, site_id)
    ) TYPE=InnoDB;

    I would like to have an auto generated id but for each site. For example:

    id site_id name
    1 1 index
    2 1 home
    3 1 article
    1 2 index
    2 2 home
    3 2 article

    Is it possible to have this done automatically by mysql or I have to lock
    the table and do a thing like that:

    LOCK TABLE sites_pages;
    SELECT MAX(id) AS page_id FROM sites_pages WHERE site_id=1;

    take the site_id value and increment by one and then INSERT...

    ?

    Are there alternatives to lock the entire table?

    Thanks in advance,

    Alex
    Alex Guest

  2. #2

    Default Re: Autoincremented id with two primary keys

    Hello Alex,
    > Hi all. I have this table:
    >
    > CREATE TABLE sites_pages (
    > id int(6) NOT NULL,
    > site_id int(4) NOT NULL,
    > name varchar(80) NOT NULL,
    > UNIQUE KEY site_id_name (site_id, name),
    > PRIMARY KEY (id, site_id)
    > ) TYPE=InnoDB;
    That's actually not "two primary key", but 1 compound primary key.
    > I would like to have an auto generated id but for each site. For example:
    >
    > id site_id name
    > 1 1 index
    > 2 1 home
    > 3 1 article
    > 1 2 index
    > 2 2 home
    > 3 2 article
    >
    > Is it possible to have this done automatically by mysql or I have to lock
    No, MySQL cannot do this automatically.
    > the table and do a thing like that:
    >
    > LOCK TABLE sites_pages;
    > SELECT MAX(id) AS page_id FROM sites_pages WHERE site_id=1;
    >
    > take the site_id value and increment by one and then INSERT...
    >
    > ?
    >
    > Are there alternatives to lock the entire table?
    Don't lock it, just try to insert your new value. It will return an
    exception
    if the value already exists, meaning you have to try again.


    --
    Martijn Tonies
    Database Workbench - development tool for MySQL, and more!
    Upscene Productions
    [url]http://www.upscene.com[/url]
    My thoughts:
    [url]http://blog.upscene.com/martijn/[/url]
    Database development questions? Check the forum!
    [url]http://www.databasedevelopmentforum.com[/url]


    Martijn Tonies Guest

Similar Threads

  1. Preserving Primary Keys in DTS
    By dj shane in forum Coldfusion Database Access
    Replies: 3
    Last Post: September 27th, 08:57 PM
  2. Tablespaces and primary keys
    By Wes in forum PostgreSQL / PGSQL
    Replies: 1
    Last Post: January 25th, 04:34 AM
  3. Deleting Primary Keys
    By Don Bryant in forum Informix
    Replies: 4
    Last Post: September 22nd, 08:26 AM
  4. Primary Keys
    By John Simmons in forum PHP Development
    Replies: 6
    Last Post: August 17th, 10:13 PM
  5. Is the use of VARCHAR(256) as Primary Keys preferred in Oracle?
    By Mark D Powell in forum Oracle Server
    Replies: 1
    Last Post: December 6th, 01:14 AM

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