SQL 2005
-
i have two tables ROOM(nbROOM primary key) : ML002 STUDENT(nbROOM) : ML002 How can i change the ROOM(nbROMM) : ML002 => ML003 =========> the STUDENT(nbROOM) must also change : ML002 =>ML003 ?:confused:
Simple - you can't. The first problem is that you are using business data for keys. And you now know why that is a problem. Your only solution now requires. 1. Create a new room row with ML003 2. Update STUDENT to point to MLO03. 3. Delete the existing ML002 record. Second problem is that what you are doing isn't logical anyways. If you have a list of rooms you don't change them unless you are in fact relabeling all of the rooms themselves (ie someone is going down the hallway in the school and putting new numbers on the doors.) In contrast if a person is assigned to the wrong room or needs to be moved then you update the person, not the room, to indicate the change. (In databases in general there is another way to do this but it wouldn't apply to room assignment.) Your solution should be ROOM(primary key, room label): {1, ML002}, {2, ML003} STUDENT(room primary key): {1} In the above you would just update '1' in the student record to be a '2'
-
i have two tables ROOM(nbROOM primary key) : ML002 STUDENT(nbROOM) : ML002 How can i change the ROOM(nbROMM) : ML002 => ML003 =========> the STUDENT(nbROOM) must also change : ML002 =>ML003 ?:confused:
I am assuming you have a foreign key constraint that is preventing you from modifying the primary key. In that case, insert new records that are based on the existing records, but with the new primary key, then delete the old records.
-
Simple - you can't. The first problem is that you are using business data for keys. And you now know why that is a problem. Your only solution now requires. 1. Create a new room row with ML003 2. Update STUDENT to point to MLO03. 3. Delete the existing ML002 record. Second problem is that what you are doing isn't logical anyways. If you have a list of rooms you don't change them unless you are in fact relabeling all of the rooms themselves (ie someone is going down the hallway in the school and putting new numbers on the doors.) In contrast if a person is assigned to the wrong room or needs to be moved then you update the person, not the room, to indicate the change. (In databases in general there is another way to do this but it wouldn't apply to room assignment.) Your solution should be ROOM(primary key, room label): {1, ML002}, {2, ML003} STUDENT(room primary key): {1} In the above you would just update '1' in the student record to be a '2'
-
i have two tables ROOM(nbROOM primary key) : ML002 STUDENT(nbROOM) : ML002 How can i change the ROOM(nbROMM) : ML002 => ML003 =========> the STUDENT(nbROOM) must also change : ML002 =>ML003 ?:confused:
-
^^~ u didn't understand my problem ROOM(nuROOM primary key, nameROOM) STUDENT(nuSTUDENT primary key,nameSTUDENT,nuROOM) :confused: this is my database !
What JSchell is saying is that your database is designed WRONG and you should fix it before you get into even more trouble. Whats more he gave you the ideas of how to fix it.
Never underestimate the power of human stupidity RAH
-
^^~ u didn't understand my problem ROOM(nuROOM primary key, nameROOM) STUDENT(nuSTUDENT primary key,nameSTUDENT,nuROOM) :confused: this is my database !