I have four tables Store, Employee, Customer, Address. I have linked the first three tables to Address. Now I want to remove this FK and reference these tables to the Address. How can I do it?
Table structure:
Store:
StoreID(PK), BranchName
Employee:
EmpID(PK), Name
Customer:
CustID(PK), Name
Address:
AddID(PK),
ID(FK to Store.StoreID, Employee.EmpID, Customer.CustID),
AddressDetail
I want to remove FK on Address.ID and just reference it to three tables.
You can remove a FOREIGN KEY constraint with an ALTER TABLE statement. For example
ALTER TABLE [Address] DROP CONSTRAINT [FK_Address_Store]
To remove a column, again, an ALTER TABLE statement.
ALTER TABLE [Address] DROP COLUMN [ID]
The data model you are trying implement is not at all clear. How many Address can be related to a Customer? Only one, or more than one? Can the same Address be associated with a Customer and an Employee? Can a specific Address be related to more than one Customer?
In my Entity Relationship models, "Address" is typically a composite attribute, and not an entity. Often, Address is a multi-valued attribute (e.g. a Customer can have more than one Address, or more than one PhoneNumber). Sometimes, an Addresses will be a particular type of Address (Shipping address, Billing address, Office address, Home address), just like phone numbers can be of a type (main, mobile, office, fax, etc.)
Without an understanding the data model, it's not possible to give you a recommendation on the changes you should make to your database tables to implement the model.