How to create a Reference Table

[gradinumcp]

Hi! I have a table in the database with 4 columns. The Autonum column is the primary key. The other 3 columns are T1, T2 and T3. T1 is an auto increment column having numbers 1,2,3....

I do an insert to insert a row into this table. I want that once the insert is done, the entry into the T1 column should be old data+1 meaning if the last entry in T1 was 5 then it should be 6 for this new insert.

Someone suggested I create a Reference table--any ideas how to do tht---what are the other options to do this??

Thanks!

 

[sleipnir214]

I'm not sure what a reference table is.

If tht T1 column is an auto_increment column, then insert a record without referencing that column and MySQL will generate a new value. Given a table created using the query:

CREATE TABLE tablename
(
T1 int unsigned auto_increment primary key,
T2 varchar(15),
T3 varchar(15)
)

then the query:

INSERT INTO tablename (T2, T3) values ('firstvalue', 'secondvalue')

will cause MySQL to insert a new record into tablename and generate a new auto_increment value for T1.


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[gradinumcp]

Thanks for your reply. However T1 is part of the primary key, so if i dont insert anything in it then it gives ERROR: Insert fails, cannot insert NULL into this column.

Also how can i make a column auto-increment in the SQL server. It does not allow me to change Identity increment to 1?

 

[sleipnir214]

You're saying "SQL Server". We are both talking about MySQL and not Microsoft SQL Server, right?


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[gradinumcp]

I am sorry I ment mysql. Well I think I figured it out, I had T1 as Varchar and not int. Once I changed it to int--it worked.

Thanks!

 

[sleipnir214]

Yep. According to

http://dev.mysql.com/doc/mysql/en/create-table.html,

an autoincrement column must be of an integer type.

I further recommend that all auto_increment columns be of an unsigned integer type.


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[gradinumcp]

Thanks a bunch for the extra info!