CODEWITHSUNDEEP
phpmysqluuid
user470760
user470760

Reputation:

Unique IDs in MySQL?

So I am working on a fairly small script for the company I work at the help us manage our servers better. I don't use MySQL too often though so I am a bit confused on what would be the best path to take.

I am doing something like this...

$sql = "CREATE TABLE IF NOT EXISTS Servers
    (
        MachineID int NOT NULL AUTO_INCREMENT,
        PRIMARY KEY(MachineID),
        FirstName varchar(32),
        LastName varchar(32),
        Datacenter TINYINT(1),
        OperatingSystem TINYINT(1),
        HostType TINYINT(1)
    )";

$result = mysql_query($sql,$conn);
check ($result);

$sql = "CREATE TABLE IF NOT EXISTS Datacenter
    (
        DatacenterID int NOT NULL AUTO_INCREMENT,
        PRIMARY KEY(DatacenterID),
        Name varchar(32),
        Location varchar(32)
    )";

$result = mysql_query($sql,$conn);
check ($result);

Basically inside the Servers table, I will be storing the index of an entry in another table. My concern is that if we remove one of those entries, it will screw up the auto incremented indexes and potentially cause a lot of incorrect data.

To explain better, lets say the first server I add, the Datacenter value is 3 (which is the DatacenterID), and we remove the id 1 (DatacenterID) Datacenter at a later time.

Is there a good way to do this?

Upvotes: 2

Views: 282

Answers (4)

Lloyd Moore
Lloyd Moore

Reputation: 3197

If you remove in the way you suggest nothing will happen. auto-increment will just add the next highest number in sequence whenever a new record is added and so will not affect any previous records.

Hope that helps.

Upvotes: 0

Stephan B
Stephan B

Reputation: 3701

Servers.Datacenter should be an INT, too, as you would store the DataCenterID in this field. Then, nothing will be mixed up when you remove some Datacenter from the second table.

Upvotes: 0

Capitaine
Capitaine

Reputation: 2005

as said by paul, it is safe to remove old row and add another new one later. The auto incremental index won't be affected by deletion.

But I would suggest instead of removing them, simply add a column 'status' and set 0, implying they are no longer in use, to keep any possible record in db.

Upvotes: 1

Paul DelRe
Paul DelRe

Reputation: 4039

Auto increment only has an effect on inserting new rows into a table. So you insert three records into database and they get assigned ids of 1, 2, and 3. Later you delete id 1, but the records at id 2 and 3 are unchanged. This says nothing of any of the server records that might be trying to reference the database id 1 record though that is now missing.

Upvotes: 2

Related Questions