Originally posted by Shinto
CREATE TABLE NOASSETTAG
TaglessNo Integer NOT NULL,
Product_Type Varchar(30) NOT NULL,
Inv_Location Varchar(10) NOT NULL,
Inv_Status Varchar(10) NOT NULL,
PRIMARY KEY (TaglessNO),
FOREIGN KEY (Asset_Owner) REFERENCES USER(UserId) ON DELETE SET NULL,
FOREIGN KEY (Inv_Location) REFERENCES LOCATION(Place) ON DELETE RESTRICT,
FOREIGN KEY (Product_Type) REFERENCES PRODUCT(Prod_Name) ON DELETE RESTRICT
CREATE TABLE LOCATION
Place Varchar(10) NOT NULL,
Room Varchar(25) NOT NULL,
These are the 2 tables i have. How do i update the place value in the Location table and update all the values in the NoAssetTag.Inv_Location at the same time? Thanks in advance
You would have to create a new LOCATION entry, update NOASSETTAG rows that refer to the old location, and then delete the original location. Alternatively, drop constraints and re-create them after update.
PS. The fact that you need to update a primary key is a sign of a questionable physical model, in my opinion.