alter table add second primary key
Dear experts,
i have 2 sybase databases.
1 used for production and 1 use for development.
all my tables have an autonumber primary key.
recently, i've taken a project of upgrading the software so that we have centralized management for many branches. and i need to add a second primary key called branchid on some of the tables.
normally when i do this in the gui i follow these steps.
1. add the new column branchid to the table (ofcourse it has to be not null because the table already has data)
2. update tablename set branchid=0 (or any value that i want)
3. modify the table so that it doesnt allow null values
4. set it as primary key (keeping the primary autonumbered pkey as well)
doing this programmatically:
1. i'm adding the column using alter table add column branchid null << successful
2. update table set branchid=0 << success
3. alter table tablename modify column branchid not null << success
4. alter table tablename add primary key (branchid) << i get an error saying that table already has a primary key
what is the syntax of altering a table from 1 pkey structure to a composite pkey structure??
i appreciate your help,
regards
p.s. i dont wanna look cheap, but this question is urgent and 95 points are all i have left on my account.
i have 2 sybase databases.
1 used for production and 1 use for development.
all my tables have an autonumber primary key.
recently, i've taken a project of upgrading the software so that we have centralized management for many branches. and i need to add a second primary key called branchid on some of the tables.
normally when i do this in the gui i follow these steps.
1. add the new column branchid to the table (ofcourse it has to be not null because the table already has data)
2. update tablename set branchid=0 (or any value that i want)
3. modify the table so that it doesnt allow null values
4. set it as primary key (keeping the primary autonumbered pkey as well)
doing this programmatically:
1. i'm adding the column using alter table add column branchid null << successful
2. update table set branchid=0 << success
3. alter table tablename modify column branchid not null << success
4. alter table tablename add primary key (branchid) << i get an error saying that table already has a primary key
what is the syntax of altering a table from 1 pkey structure to a composite pkey structure??
i appreciate your help,
regards
p.s. i dont wanna look cheap, but this question is urgent and 95 points are all i have left on my account.
ASKER
hey angelll, thanks for ur fast reply
let me give u an example:
i have a transactions table in 2 branches
TBLTRANS
ID PKEY (autonumber)
qty
product
price
branchid
in branch 1 i have 3 transactions so far
1 10 coke 1
2 25 book 1
3 15 pens 1
in branch 2 i have 4 transactions so far (but the ids are the same because their auto numbered)
1 2 pens 2
2 7 balls 2
3 4 cups 2
4 12 phone 2
my software will import the table tbltrans from all branches into the tbltrans of branch 1so that i'll have all the centralized data.
1 10 coke 1
2 25 book 1
3 15 pens 1
1 2 pens 2 << this will trigger a primary key constraint violation if the branchid is not a pkey
2 7 balls 2
3 4 cups 2
4 12 phone 2
before i drop the primary key and add a second one... i have a small question
QUOTE:
"however, in terms of understandings:
you can have 1 primary key and several other unique indexes on 1 table
you might also need to add the field to the existing primary key"
what do u mean by the above? and does it answer my needs? and what is the sql command for it?
i appreciate your help
regards,
dan
let me give u an example:
i have a transactions table in 2 branches
TBLTRANS
ID PKEY (autonumber)
qty
product
price
branchid
in branch 1 i have 3 transactions so far
1 10 coke 1
2 25 book 1
3 15 pens 1
in branch 2 i have 4 transactions so far (but the ids are the same because their auto numbered)
1 2 pens 2
2 7 balls 2
3 4 cups 2
4 12 phone 2
my software will import the table tbltrans from all branches into the tbltrans of branch 1so that i'll have all the centralized data.
1 10 coke 1
2 25 book 1
3 15 pens 1
1 2 pens 2 << this will trigger a primary key constraint violation if the branchid is not a pkey
2 7 balls 2
3 4 cups 2
4 12 phone 2
before i drop the primary key and add a second one... i have a small question
QUOTE:
"however, in terms of understandings:
you can have 1 primary key and several other unique indexes on 1 table
you might also need to add the field to the existing primary key"
what do u mean by the above? and does it answer my needs? and what is the sql command for it?
i appreciate your help
regards,
dan
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
however, in terms of understandings:
you can have 1 primary key and several other unique indexes on 1 table
you might also need to add the field to the existing primary key
so, reading what you have already, you need to drop the existing primary key, and create a new primary key, with the current primary key field + the new field added to it.