0

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.

marc_s
  • 675,133
  • 158
  • 1,253
  • 1,388
DhavalR
  • 1,361
  • 2
  • 25
  • 53

1 Answers1

1

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.

spencer7593
  • 99,718
  • 14
  • 99
  • 122
  • If single address for each record, I should add columns for address to all the tables? – DhavalR Apr 22 '16 at 19:21
  • Or, add a foreign key column in Employee, [Addresss_AddID] with the same datatype (int?) as AddID column in Address table. And define a constraint [FK_Emplouyee_Address] foreign key [Address_AddID] references [Address]([AddID]). You can repeat that same pattern for [Customer] and [Store] adding a foreign key column and a foreign key constraint. – spencer7593 Apr 22 '16 at 19:24
  • That means I do change `Employee` to `EmpID(PK), AddID(FK Address.AddID),...`? And for the others. – DhavalR Apr 22 '16 at 19:36
  • 1
    That's how I would do it, but the notation you are using is strange. The pattern we follow, for foreign key columns, we name those as the referenced table and the primary key of the table. For example, in my [employee] table, I would add a column named [address_id]. (But that's because in my [address] table, the primary key name would be [id]. And I would name of the foreign key constraint with the name of the referencing table and the referenced table... [FK_employee_address]. That's just the convention we follow in our shop. Your shop may follow different conventions for naming. – spencer7593 Apr 22 '16 at 20:08
  • 1
    But again,the columns and foreign key constraints we implement depends on the Entity Relationship data model, with some possible denormalization tweaks for performance reasons. If I only need one address, and that address is related to only one Customer, we wouldn't store that in a separate table. We implement "address" as a composite attribute in the Customer table. (i.e. add columns to the Customer table named addr_line1, addr_line2, addr_city, addr_state, addr_postal_code, et al. We just look at those individual columns as if they were combined, into a single attribute named "addr".) – spencer7593 Apr 22 '16 at 20:35
  • In your model, an "Address" may be an entity (ie a person, place, thing, concept or event, which can be uniquely identified, is important to the business, and we can store information about.) If it's an entity, then our first cut at a design, it should be its own table. If it turns out not to be an entity and is an "attribute" (i.e. information we need to store about an entity) then we typically add a column in an entity table. If it's a multi-valued attribute (i.e. an instance of entity can have multiple values of the attribute) we can create a separate table to store multiple values. – spencer7593 Apr 23 '16 at 03:26
  • Thanks for the detailed explanation. – DhavalR Apr 23 '16 at 04:02