Problems with foreign keys

Question

Since I'm new to SQL Server, I think I need a little help here... Since I've rewritten the code below many times there might be other errors as well, sorry about that. Removed the 'GO' for now.

Problem:

I don't know how to create a foreign key from "model" in the table Cars to the table Model.

My broken code:

create table [Cars]
(
   [id] [int] not null primary key identity,
   [name] [nvarchar](50) not null,
   [weight] [int] not null,
   [length] [int] not null,
   [model] [nvarchar](50) not null,
   [color] [nvarchar](50) not null
);

create table [Colors]
(
   [id] [int] not null primary key identity,
   [name] [nvarchar](50) not null
);

create table [Model] 
(
    [id] [int] primary key not null identity,
    [name] [nvarchar](50) not null
);

insert into Cars(name, weight, length, model, color)
values('Ferrari', '1500', '4000', '360', 'Red');

insert into Colors values('Red');
insert into Colors values('Blue');
insert into Colors values('Yellow');

insert into Model values('Volvo');
insert into Model values('Fiat');
insert into Model values('Saab');

alter table Model
add foreign key (id)
references Cars(id)

alter table Colors
add foreign key (id)
references Cars(id)

This model is flawed and you should take a step back and rethink it. Using a foreign key relationship on the Color table to enforce that a color id exists as a car id doesn't make any sense at all. I'd suggest you read up on modeling a bit. — jpw, Sep 07 '14 at 19:32

Tony · Answer 1 · 2014-09-08T16:21:43.843

0

I advise against naming all your primary keys the same (ID) as this runs in to problems when creating foreign keys.

Your table Cars has model as nvarchar, which is unnecessary if you are using surrogate keys. Change the data type to int, the name to model_id and add a constraint to the table:

ALTER TABLE Cars
ADD CONSTRAINT FK$Model$id FOREIGN KEY (model_id)
REFERENCES Model(id)

As others have mentioned, your use of a foreign key in the Colors table is also incorrect.

edited Sep 08 '14 at 16:21

answered Sep 07 '14 at 20:35

Tony

9,672
3
47
75

I advise against @Tony's advice (in regards to ID as Primary Key name), as it means you are constantly guessing what the Primary Key of every table is. In practice this is likely to be a policy set by your Senior DBA, and you violate his policies at your peril. Both naming schemes are very viable, many developers get religious in their affirmation of one over the other, and the only true sin is inconsistency. Choose either one and use it everywhere, until the end of that lifetime/project. – Pieter Geerkens Sep 07 '14 at 23:48
@Pieter - my advice is to NOT have every PK as [ID], I think you may have misread my answer. – Tony Sep 08 '14 at 16:06
And mine is to HAVE every PK as [ID]. I did not misunderstand at all. Constantly typing TableName.TableNameID I see as simply a recipe for Carpal Tunnel. – Pieter Geerkens Sep 08 '14 at 21:37
@PieterGeerkens - Ultimately it's personal preference, there are [other](http://stackoverflow.com/questions/863872/id-or-tablenameid-as-primary-key-entity-identifier) [questions](http://stackoverflow.com/questions/10436953/naming-primary-keys-id-vs-something-id-in-sql) on SO discussing the merits of both. However, if your main reason for using [id] is simply to reduce the amount of typing you need to get some better SQL programming tools, such as Red Gate's _SQL Prompt_ (BTW I have no affiliation with Reg Gate) – Tony Sep 08 '14 at 22:03

score 0 · Accepted Answer · answered Sep 15 '14 at 21:23

0

This code managed to solve the problem I think, several of you people here did contribute, thank you.

alter table Cars
add constraint fk_colors foreign key (colorid)
references colors([id])

answered Sep 15 '14 at 21:23

Varg

59
1
8

score -1 · Answer 3 · answered Sep 07 '14 at 18:27

-1

--Add column in Model as Car_ID
--then use

ALTER TABLE Model
ADD CONSTRAINT FK_CarsID FOREIGN KEY(Car_ID) REFERENCES Cars(ID)

answered Sep 07 '14 at 18:27

Dgan

10,077
1
29
51

1

Why would you want to store the id of a car in the model table? – jpw Sep 07 '14 at 19:34
@jpw because 1 Car Can Have Multiple Models and without adding how can we maintain relation ships between two tables and i think in above question model and colors are not required in Cars tables it should be removed – Dgan Sep 08 '14 at 05:07

Problems with foreign keys

3 Answers3