Jump to content


Photo

updating primary key after its been created


  • Please log in to reply
4 replies to this topic

#1 leeming

leeming
  • Members
  • PipPipPip
  • Advanced Member
  • 93 posts

Posted 08 July 2006 - 08:47 PM

hey, ive tried to run this query both in myPHPadmin (point and click) aswell as the sql code stright in, but neither work...
Can i not change field properties on a primary key field? im trying to put NULL on, so i can insert a user id of "0".

  mysql_query("ALTER TABLE `member` CHANGE `mid` `mid` INT( 15 ) NULL AUTO_INCREMENT")or die('1 : '.mysql_error());

N.B 'mid' been my id field, (MemberID)
Im not perfect ;) lol

#2 robos99

robos99
  • Members
  • PipPipPip
  • Advanced Member
  • 55 posts

Posted 08 July 2006 - 09:19 PM

I'm not 100% positive but I don't think you can have a NULL value on a primary key, or an auto increment column. FYI, in MySQL, 0 and NULL are not the same. You could still have a value of 0 without declaring the column as NULL. But I'm pretty sure you can't have a primary key with a NULL value, and you can't have an auto increment column with a 0 value.....but I may be wrong about that last one. Try that and see if it works.

#3 leeming

leeming
  • Members
  • PipPipPip
  • Advanced Member
  • 93 posts

Posted 09 July 2006 - 01:11 AM

well i only wanted to know, so that my member id "0" can be shown as "ADMIN"... if not, i will just have to hard code all the pages linked to showing "member info" with some kind of IF(mid == 0) kinda of statment... just would of liked to be able to just have the "ADMIN" 'user' as in the database
Im not perfect ;) lol

#4 ToonMariner

ToonMariner
  • Members
  • PipPipPip
  • Advanced Member
  • 3,342 posts
  • LocationNewcastle upon Tyne, UK

Posted 09 July 2006 - 01:26 AM

You seem to be missing the idea behind primary keys - they are primarily there to identfy a unique record.

You don't need to do anything with them as the database does that itself. If you try to do what you have suggested and have more than 1 admin then the database would NEVER let you have 2 primary key values of 0 - it couldn't be a key then!

You are better off adding a field to the table indicating the users permissions level....
follow me on twitter @PHPsycho

#5 leeming

leeming
  • Members
  • PipPipPip
  • Advanced Member
  • 93 posts

Posted 09 July 2006 - 01:32 AM

You seem to be missing the idea behind primary keys - they are primarily there to identfy a unique record.

You don't need to do anything with them as the database does that itself. If you try to do what you have suggested and have more than 1 admin then the database would NEVER let you have 2 primary key values of 0 - it couldn't be a key then!

You are better off adding a field to the table indicating the users permissions level....


no just 1 admin account.. ok i will explain a bit...


parts of my site.. eg in site mail system... shows who the mail is from, and if i had memberid for "admin" = '0' then it would act like a normal database call..
if i cant do what im trying with the id for = '0' then i will have to put in (every where, "Admin" account details are shown.. eg havings IF(user = admin) every where...)
Im not perfect ;) lol




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users