Best way to set up this Mysql? (primary key?) - Tilted Forum Project Discussion Community

Realizm · 02-27-2004, 05:34 PM

Me and a friend are technicians and I'm wanting to set up a database that contains info on the items we work on.

Products that are the exact same (model number, type) are grouped by "Item Number" which we have created over time.

The database would include spaces for the item number, model number, etc. We work with several different objects though and there are thousands of differen t products so many many item numbers.

I have been reading a little about how a "primary key" in mysql is like something unique.. like a way of tracking, I guess?

would it be good for me to set up a primary key as our item number?

sailor · 02-27-2004, 06:50 PM

If that number is *always* unique and there wont be any repetitions of it in the table, then yes. Tracking would be a pretty good way to look at it.

Its been a while since Ive done any DB work, Ill let others elaborate.

ratbastid · 02-28-2004, 08:44 PM

I'm not real clear from what you've said whether it'll be helpful or not, but you can set up INT()-typed primary key fields to be auto-incrmenting. You can leave the field out of your INSERT statement, or insert NULL into the field, and the database will automatically give you the next number in that field.

Code:

CREATE TABLE Demonstration (
  rownumber INT(5) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  modelnumber VARCHAR(255),
  type VARCHAR(255)
);

(I am renowned far and wide for my properly capitalized SQL statements.)

Realizm · 02-29-2004, 11:45 AM

thanks

Nooze2k · 02-29-2004, 10:27 PM

Quote:

(I am renowned far and wide for my properly capitalized SQL statements.)

Good to see I'm not the only one who does this also. Always thought it was much more cleaner, and that's the way I was taught anyways; although they've been more lax with SQL style than C++/VB/PHP etc style (almost as if SQL isn't a real language in comparison of other languages). Does anyone else notice this as well?

gigawatz · 03-05-2004, 06:38 AM

As far as a primary key, it really just depends on your database structure. If you make your databases in a NORMALIZED fashion, then autonumbers have their place, however, I cannot proscribe a suggestion on your primary key w/o knowing your whole structure.

However, if you are asking "what is a primary key", then that's easy. A primary key (1 or more columns) is a value that the database will enforce uniqueness. The RDBMS will not permit you to put duplicate values in the specified column(s). Should you use Primary keys? Yes. Should you use indexs? Yes.

My suggestion would be to find some SQL tutorials and read up on the finer points of the language before constructing your database and application. If you save you a lot of time in rebuilding later.

As far as the capitalization in SQL, I am as lax as can be, other languages are strict and I don't have a problem conforming, however, I've never found a reason (readability, functionality, ect) to even try to change my mind. Whatever the fingers hit is what my command looks like...

Realizm · 03-05-2004, 08:42 AM

I'm going to set the primary key but I can't figure out how to alter my column to make it a primary key.

Right now it is just set as text..

what's the command to make that column primary key?

ALTER TABLE mytable ALTER COLUMN column ........ ?

Rawb · 03-10-2004, 11:27 PM

http://www.mysql.com/doc/en/ALTER_TABLE.html

Speficily(sp):
To add an index on column d, and make column a the primary key:

mysql> ALTER TABLE t2 ADD INDEX (d), ADD PRIMARY KEY (a);

02-27-2004, 05:34 PM	#1 (permalink)
Realizm Insane Location: A fuzzy cloud.	Best way to set up this Mysql? (primary key?) Me and a friend are technicians and I'm wanting to set up a database that contains info on the items we work on. Products that are the exact same (model number, type) are grouped by "Item Number" which we have created over time. The database would include spaces for the item number, model number, etc. We work with several different objects though and there are thousands of differen t products so many many item numbers. I have been reading a little about how a "primary key" in mysql is like something unique.. like a way of tracking, I guess? would it be good for me to set up a primary key as our item number?

02-27-2004, 06:50 PM	#2 (permalink)
sailor beauty in the breakdown Location: Chapel Hill, NC	If that number is always unique and there wont be any repetitions of it in the table, then yes. Tracking would be a pretty good way to look at it. Its been a while since Ive done any DB work, Ill let others elaborate. __________________ "Good people do not need laws to tell them to act responsibly, while bad people will find a way around the laws." --Plato

02-28-2004, 08:44 PM	#3 (permalink)
ratbastid Darth Papa Location: Yonder	I'm not real clear from what you've said whether it'll be helpful or not, but you can set up INT()-typed primary key fields to be auto-incrmenting. You can leave the field out of your INSERT statement, or insert NULL into the field, and the database will automatically give you the next number in that field. Code: CREATE TABLE Demonstration ( rownumber INT(5) NOT NULL PRIMARY KEY AUTO_INCREMENT, modelnumber VARCHAR(255), type VARCHAR(255) ); (I am renowned far and wide for my properly capitalized SQL statements.)

03-10-2004, 11:27 PM	#8 (permalink)
Rawb Crazy Location: Salt Town, UT	Here is the MySQL guide for it http://www.mysql.com/doc/en/ALTER_TABLE.html Speficily(sp): To add an index on column d, and make column a the primary key: mysql> ALTER TABLE t2 ADD INDEX (d), ADD PRIMARY KEY (a);

02-29-2004, 11:45 AM	#4 (permalink)
Realizm Insane Location: A fuzzy cloud.	thanks

03-05-2004, 06:38 AM	#6 (permalink)
gigawatz Crazy Location: Pittsburgh	As far as a primary key, it really just depends on your database structure. If you make your databases in a NORMALIZED fashion, then autonumbers have their place, however, I cannot proscribe a suggestion on your primary key w/o knowing your whole structure. However, if you are asking "what is a primary key", then that's easy. A primary key (1 or more columns) is a value that the database will enforce uniqueness. The RDBMS will not permit you to put duplicate values in the specified column(s). Should you use Primary keys? Yes. Should you use indexs? Yes. My suggestion would be to find some SQL tutorials and read up on the finer points of the language before constructing your database and application. If you save you a lot of time in rebuilding later. As far as the capitalization in SQL, I am as lax as can be, other languages are strict and I don't have a problem conforming, however, I've never found a reason (readability, functionality, ect) to even try to change my mind. Whatever the fingers hit is what my command looks like...

03-05-2004, 08:42 AM	#7 (permalink)
Realizm Insane Location: A fuzzy cloud.	I'm going to set the primary key but I can't figure out how to alter my column to make it a primary key. Right now it is just set as text.. what's the command to make that column primary key? ALTER TABLE mytable ALTER COLUMN column ........ ?