So, you have compound keys and presumably implicit relationships which you want to replace with proper primary key columns and foreign keys.
The way to approach this is in five easy pieces:
- Add the new columns to the tables.
- Populate the primary key columns.
- Populate the foreign key columns.
- Add the constraints.
- Drop th enugatory columns.
I will work through this using Oracle syntax, but the same principles work in SQL Server.
SQL> select * from grandparent
2 /
TE G_COUNTER GF
-- ---------- --
t1 1 ga
t2 1 gb
t2 2 gc
t3 1 gd
SQL> select * from parent
2 /
TE G_COUNTER P_COUNTER PF
-- ---------- ---------- --
t1 1 1 pa
t1 1 2 pb
t1 1 3 pc
t2 1 1 pd
t3 1 1 pe
SQL> select * from child
2 /
TE G_COUNTER P_COUNTER C_COUNTER CF
-- ---------- ---------- ---------- --
t1 1 1 1 ca
t1 1 1 2 cb
t1 1 1 3 cc
t2 1 1 1 cd
t2 1 1 2 ce
t3 1 1 1 cd
6 rows selected.
SQL>
Step 1: Add the new columns
SQL> alter table grandparent
2 add g_id number
3 /
Table altered.
SQL> alter table parent
2 add p_id number
3 add g_id number
4 /
Table altered.
SQL> alter table child
2 add c_id number
3 add p_id number
4 /
Table altered.
SQL>
Step 2: Populate the primary keys
SQL> update grandparent
2 set g_id = rownum
3 /
4 rows updated.
SQL> update parent
2 set p_id = rownum
3 /
5 rows updated.
SQL> update child
2 set c_id = rownum
3 /
6 rows updated.
SQL>
Step 3: Populate the foreign keys
SQL> update parent p
2 set g_id = ( select g_id
3 from grandparent g
4 where g.tel = p.tel
5 and g.g_counter = p.g_counter)
6 /
5 rows updated.
SQL> update child c
2 set p_id = ( select p_id
3 from parent p
4 where p.tel = c.tel
5 and p.g_counter = c.g_counter
6 and p.p_counter = c.p_counter)
7 /
6 rows updated.
SQL>
Step 4: Add the constraints
SQL> alter table grandparent
2 modify g_id not null
3 add constraint g_pk primary key (g_id) using index
4 /
Table altered.
SQL> alter table parent
2 modify p_id not null
3 add constraint p_g_fk foreign key (g_id)
4 references grandparent (g_id)
5 add constraint p_pk primary key (p_id) using index
6 /
Table altered.
SQL> alter table child
2 modify c_id not null
3 add constraint c_p_fk foreign key (p_id)
4 references parent (p_id)
5 add constraint c_pk primary key (c_id) using index
6 /
Table altered.
SQL>
Step 5: Drop the nugatory columns
I am presuming that GRANDPARENT (TEL, G_COUNTER) represents some kind of business key. So rather than dropping them I suggest you add a unique constraint to enforce the rule. The same might be true for PARENT(G_ID, P_COUNTER) and CHILD(G_ID, C_COUNTER). You know your data better than I do. Hence the following statements are indicative of the sort of thing you might want to do; adjust them according to your needs.
SQL> alter table grandparent
2 add constraint g_uk unique (tel, g_counter) using index
3 /
Table altered.
SQL> alter table parent
2 add constraint p_uk unique (g_id, p_counter) using index
3 /
Table altered.
SQL> alter table parent
2 drop column tel
3 /
Table altered.
SQL> alter table parent
2 drop column g_counter
3 /
Table altered.
SQL> alter table child
2 drop column tel
3 /
Table altered.
SQL> alter table child
2 drop column g_counter
3 /
Table altered.
SQL> alter table child
2 drop column p_counter
3 /
Table altered.
SQL> alter table child
2 add constraint c_uk unique (p_id, c_counter) using index
3 /
Table altered.
SQL>
Let's check the tables:
SQL> select * from grandparent
2 /
TE G_COUNTER GF G_ID
-- ---------- -- ----------
t1 1 ga 1
t2 1 gb 2
t2 2 gc 3
t3 1 gd 4
SQL> select * from parent
2 /
P_COUNTER PF P_ID G_ID
---------- -- ---------- ----------
1 pa 1 1
2 pb 2 1
3 pc 3 1
1 pd 4 2
1 pe 5 4
SQL> select * from child
2 /
C_COUNTER CF C_ID P_ID
---------- -- ---------- ----------
1 ca 1 1
2 cb 2 1
3 cc 3 1
1 cd 4 4
2 ce 5 4
1 cd 6 5
6 rows selected.
SQL>
Maintaining the primary keys requires different techniques in different flavours of DBMS. Oracle uses sequences, SQL Server uses AUTO INCREMENT. That is a separate question, which has already been weel covered in SO.