[lmei007]

we can use integer as a table unique primary key and we can let the database to assign an unique number to it via the auto_increment property. Saying we can have a custId integer field as the only primary key and let the MySQL database automatically increase one each time a new customer is inserted. But I am not clear about this concept.

1. does that auto_increment property is defined in SQL or in DBMS or in individual database implementation?

2. in the insert SQL statement we should ignore that field (custId) because database will automatically assign a value to it?

3. if my data is huge, is there any chance the integer value gets overflow if the database keep increase it?

4. When we should use the auto_increment feature and when we should not use it?

5. After compare the output sql file, I noticed that some modeler software support such feature but others did not. what does this mean?

[r937]

1. the auto_increment feature is not part of standard SQL

however, most database systems find it very useful, and have implemented something for it

MySQL: auto_increment
SQL Server: identity
Oracle: sequence
etc.

2. yes

3. yes, but if you use INTEGER then you will typically need to redesign your database long before you hit the limit

and if you use BIGINT, i guarantee you will never have too many rows

4. when you need a surrogate key -- i'm sorry if this is not a big explanation, so you should do some research on that term

5. it might mean you need to choose your modelling software with care

[blindman]

1) Implementation of Identity columns varies between database platforms.

2) Yes.

3) Perhaps sometime shortly before the Earth is engulfed by the Sun.

4) Sometimes you should use it some of the time. Other times you should use it either none of the time, or all of the time. Depends on what time it is.

5) It means some software is better than others.

[r937]

i am amazed at how closely our answers agree, blindman

thank $deity you did not answer "4. all the time" (but just hang on, pat will be along in a moment, he'll do it)

[blindman]

I confess. I copied your answers while looking over your shoulder.