Forum Moderators: open
ALTER TABLE `tMobileHomes` ADD FOREIGN KEY ( `Propertyid` ) REFERENCES `casherzc_realestate`.`tProperty` (
`Propertyid`
);
but when i update Propertyid on table tProperty it dose not update on tMobileHomes, what I'm doing wrong
please help thanks
You need to setup a TRIGGER to accomplish what you apparently want to do.
Before you can insert a row in tMobileHomes for tMobileHomes.PropertyID=X there must first exist a row in in tProperty where tProperty.PropertyID=X.
So if you are adding a new property that is of type MobileHome then you would have to make two inserts to add that single property:
1) First insert a new row in tProperty with PropertyType=1 (if 1=MobileHome). If tProperty.PropertyID is an identity/autoincrement field then the DB will assign a unique value for PropertyID when it inserts the row in tProperty.
2) Get the identity/autoincrement PropertyID value that was created by the DB when you inserted the row in tProperty in step 1.
3) Next insert a new corresponding row into tMobileHomes with tMobileHomes.PropertyID = the identity/autoincrement PropertyID value from tProperty
Using SQL SERVER you would do something like
DECLARE @lvMyPropertyID INT
INSERT INTO tProperty (PropertyType, ListingPrice,...) VALUES (1, 25000,...)
SET @lvMyPropertyID=@@IDENTITY
INSERT INTO tMobileHomes (PropertyID, Width, Length,...) VALUES (@lvMyPropertyID, 14, 70,...)
This would create a property that is of type mobile home (assuming a PropertyType=1 means Mobile Home) with a listing price of $25,000. The SET command grabs the IDENTITY value created by the previous INSERT statement and assigns it to a local variable @lvMyPropertyID. The 2nd INSERT then adds that property to tMobileHomes with the Foreign Key reference to tProperty.PropertyID, a width of 14 ft, a length of 70 ft, etc.
