SQL Syntax using CONCAT

Question

I have a table T1 with some fields like

id,key_id ,name,date etc..

id is a auto incrementing field
key_id is a value obtained from id like prefix/id
Prefix is constant for all the rows and just id changes which is the id for the corresponding row..

It looks like something like this

$sql = " ALTER TABLE T1 AUTO_INCREMENT = 1234567890, 
           ADD id BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT ";

UPDATE T1
   SET key_id = CONCAT('12.345','/',id)

Now due to some reasons i want to create a new table T2 which would be the parent of T1 and id would be the primary key T2 and Key_id would become primary key of T1. T2 should also include other two fields like key_id and name..

ok the main reason for parent table is : i might create another table T3 which will also have id and id should have different values for T1 and T3 so i want to maintain a master table which has id as a primary key and other child tables will fetch the ids only from master table..

How can I do this?

Sterex · Accepted Answer · 2011-07-07 19:58:24Z

1

Why would you want to store a redundant data in another column "key_id"?

Since the prefix is constant, the id is enough. Perform the concatenation in your code.

Also, that way, you can have id as primary key for both tables and get the key_id by prefixing the "Prefix".

answered Jul 7, 2011 at 19:58

Sterex

1,0281 gold badge13 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

OMG Ponies Over a year ago

key_id is likely only for display to users, and can be changed at will (user whim) while not affecting referential integrity. Been there, done that...

Dumb_Shock Over a year ago

ok the main reason for parent table is : i might create another table T3 which will also have id and id should have different values for T1 and T3 so i want to maintain a master table which has id as a primary key and other child tables will fetch the ids only from master table..

Sterex Over a year ago

@OMG: If it can be changed at will, how will you maintain it as unique? OP wants it as primary key for another table.

OMG Ponies Over a year ago

@Sterex: Unique constraint; key_id shouldn't be the PK if the approach is what I'm talking about.

Sterex Over a year ago

@razz: Can you please explain the logic behind having different values for primary keys (key_id) which are based off the same value(id)? From what I understand (until now), the database design is a bit screwed up.

|

Collectives™ on Stack Overflow

SQL Syntax using CONCAT

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related