Create foreign key with non unique column in SQL Server

Question

I am dealing with a table that contains both cars and owners (table CO). I am creating another table to contain attributes for an owner (table OwnerAttributes), that a user can assign to through a GUI. My problem lies in the fact that owners are not unique and since I am using SQL Server I cannot create a foreign key on them. There is an id in the table, but it identifies the car and owner as a whole.

The idea I had to get around this problem is to create a new table (table Owners) that contains distinct owners, and then adding a trigger to table CO that would update the Owners with any changes. I can then use table Owners for my OwnerAttributes table and solve my problem.

The question I want answered is if there is a better way to do this?

I am using a preexisting database, that is heavily used by an old application. The application is hooked up to use the table CO for owners and cars. There also exists several other tables that use the CO table. I wish I could split the table into Owners and Cars, but the company doesn't want me to spend all my time doing it as there are several more features I need to add to the application.

Sounds like you are designing the [OwnerAttribute] table using the Entity Attribute Value pattern. Perhaps have a read about the pros / cons of this approach here: http://stackoverflow.com/questions/870808/entity-attribute-value-database-vs-strict-relational-model-ecommerce-question — Kane, Feb 17 '12 at 23:07
I wrote a blog post about it as well. Not sure if it's relevant to the op but for anyone searching about EAV it might be worth a look (particularly the discussion that ensued) : https://sqlblog.org/blogs/aaron_bertrand/archive/2009/11/19/what-is-so-bad-about-eav-anyway.aspx — Aaron Bertrand, Feb 17 '12 at 23:23

score 2 · Answer 1 · answered Feb 17 '12 at 23:09

2

You should create Owner table, Car table, OwnerCar table(if person can has a few cars). Owner table contains fields, that describe owner(owner properties)

answered Feb 17 '12 at 23:09

Balconsky

2,234
3
26
39

2

If a Car can be owned just by one person (it seems that's it current state), you don't need the OwnerCar table, just add the fk_owner to Car table – Amir Pashazadeh Feb 17 '12 at 23:13
I am using a per-existing database, that is heavily used by an old application. The application is hooked up to use the table CO for owners and cars. I wish I could split the table into Owners and Cars, but the company doesn't want me to spend all my time doing that. – Joshua5822 Feb 17 '12 at 23:19
you create the structure above and then create a view with the old name fo the CO table. Old code still works (but is now referncing the view) and going forward you can chandle things properly. – HLGEM Feb 17 '12 at 23:45

Professor Falken · Accepted Answer · 2012-02-17T23:22:25.140

Your thoughts on the Owners table are on the right track! Your problem is because your schema is not normalized. It's the fact you're storing two things (cars, and owners) in one table (your table CO).

You are correct that you should make an Owner table, but you should then remove the Owner information from the CO table entirely, and replace it with a foreign key to the Owners table.

So you want something like this:

CREATE TABLE Owner (
  ownerID int not null primary key indentity(1,0),
  FirstName varchar(255),
  LastName varchar(255),
  /* other fields here */
)
GO
CREATE TABLE Car
  carID int not null primary key identity(1,0),
  ownerID int not null references Owner(ownerID),
  /* other fields go here */
GO

/* a convenience, read only view to replace your old CAR OWNER table */
CREATE VIEW Car_Owner AS
  SELECT c.*, o.FirstName, o.LastName FROM Car c INNER JOIN Owner o ON c.ownerID = o.ownerID

Now, you have everything properly normalized in SQL. A view has given you back the car_owner as one thing in a pseudo-table.

But the real answer is, normalize your schema. Let SQL do what it does best (relate things to other things). Combining the two things on one table will just lead to more problems like you're encountering downstream.

Hopefully this answer seems helpful and not condescending, which is what I was going for! I have learned the hard way that this approach (normalize everything, let the database do some extra work to retrieve/display/insert it) is the only one that works out in the end.

After some discussions with the lead developer at the company it was decided this was the way to go, but due to time restraints I won't be implementing this. The amount of time to fix and test the application would take too much time for too little gain. — Joshua5822, Feb 21 '12 at 20:40

Create foreign key with non unique column in SQL Server

2 Answers2