Basic SQL Question

Question

Basic SQL Question

porterdl

SSC Journeyman

Points: 75
More actions
April 3, 2020 at 5:18 pm

#3740273
I am new to SQL and I am having a hard time finding resources on a basic question.
I have two simple tables. I don't understand how to make the primary key and foreign key the same. The Primary key is an increment). If I were to add a record, it would say "sorry you cant insert nulls in the foreign key column VehicleID." Shouldn't the foreign key automatically be copied from the VehicleID in the Employee table to the VehicleID in the Vehicle Table? My goal is to add an employee to the database, and their vehicle info and have relationship between them. I am working in .NET (The Server Explorer Tool)
EmployeeTable:
```
CREATE TABLE [dbo].[Employee] (
    [EmpoyeeID] INT           IDENTITY (1, 1) NOT NULL,
    [FirstName] NVARCHAR (50) NULL,
    [LastName]  NVARCHAR (50) NULL,
    [StartDate] DATE          NULL,
    [VehicleID] INT           NOT NULL,
    PRIMARY KEY CLUSTERED ([EmpoyeeID] ASC),

    CONSTRAINT [FK_VehicleID] FOREIGN KEY ([VehicleID]) 
REFERENCES [dbo].[Vehicle] ([VehicleID])
```
VehicleTable:
```
CREATE TABLE [dbo].[Vehicle] (
    [VehicleID] INT           NOT NULL,
    [VIN]       INT           NULL,
    [Make]      VARCHAR (50)  NULL,
    [Model]     VARCHAR (50)  NULL,
    [Color]     NVARCHAR (50) NULL,
    PRIMARY KEY CLUSTERED ([VehicleID] ASC)
);
```

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63428 More actions · Answer 1

wait... this design is off.

Taking a step back... Can you explain the relationship between Person and Vehicle? Can one person own more than one vehicle? Can one vehicle be owned by more than one person at once? How you would model this in the database depends on how many Persons can own a single Vehicle and how many Vehicles a single Person can own.

This reply was modified 4 years ago by pietlinden.

porterdl SSC Journeyman Points: 75 More actions · Answer 2

Thanks for your response! So the concept I have is that I have drivers that transport clients. I call the drivers employees. These employees can technically "own" more than one vehicle personally, but they only drive one for our company really. If I wanted to make a web form in to enter in a new employees information into a database, I would need to enter their personal info, and their vehicle info. Right now I just make two tables with no relationship whatsoever, but I thought that I should have two separate tables for each thing. I am not really sure why I should do this other than having duplicates? but I am trying to follow best practices rather than have one giant table. Is this wrong? My database design is probably confusing and wrong though, I admit. I am learning.

Primary keys and Foreign keys seem to be useful for matching EXISTING data. But I don't understand what happens if you have no data yet.

This reply was modified 4 years ago by porterdl.

pietlinden SSC Guru Points: 63428 More actions · Answer 3

"[Drivers] can technically "own" more than one vehicle personally, but they only drive one for our company really."

I would probably have one table for Drivers and one for Vehicles, and then in the Vehicle table have a foreign key back to Driver...

CREATE TABLE Driver ( DriverID INT IDENTITY PRIMARY KEY,

LicenseNo CHAR(15)…);

CREATE TABLE Vehicle (VIN CHAR(25) PRIMARY KEY,

VehicleDriverID INT NOT NULL

FOREIGN KEY VehicleDriverID REFERENCES Driver(DriverID));

... this means that each Vehicle can be owned by /driven by only one driver, but one driver can drive many (of his own) vehicles.

You can insert a value into Driver and capture the primary key value into a variable and then insert that value into the Vehicle(OwnerID) column.

porterdl SSC Journeyman Points: 75 More actions · Answer 4

Thank you for your time, I will play with this and see if I can understand it more. I appreciate your time with this. VIN as ID makes more sense to me too.

pietlinden SSC Guru Points: 63428 More actions · Answer 5

If a driver can NEVER drive more than one car, and you don't care about the car if it doesn't belong to that driver, then you could incorporate the Car into the Driver table... but do you need to keep information that's strictly about the car, and may only be tangentially related to the driver? Like Inspections, or maybe Insurance?

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 6

What I'm waiting for is to find out when you have an employee that owns more than one car. You need a "Bridge Table" that assigns vehicles to owners. And, don't expect the PK/FK relationship to auto-magically be filled in. That's what a program is for. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

porterdl SSC Journeyman Points: 75 More actions · Answer 7

Thank you both! I think I was thinking about it with the wrong logic, I kept thinking that IDs would automatically fill but my confusion was mostly with surrogate keys i think. Thanks again! This helps

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 8

Thanks for the feedback but I'm, actually pretty late with my observation. pietlinden pretty much said in his first post what I was talking about and I simply didn't read his post.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pietlinden SSC Guru Points: 63428 More actions · Answer 9

pietlinden

SSC Guru

Points: 63428

April 4, 2020 at 12:55 am

#3740362

"Pfffft! *he* wrote it??? Not reading that!!"

LOL

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 10

pietlinden wrote:

"Pfffft! *he* wrote it??? Not reading that!!"
LOL

What I meant was that you posted the correct answer first and I missed that. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Collins SSCrazy Points: 2068 More actions · Answer 11

porterdl wrote:

VIN as ID makes more sense to me too.

porterdl wrote:

I kept thinking that IDs would automatically fill but my confusion was mostly with surrogate keys i think.

Imo there is an (at least) equally valid point of view which would assert the VIN is not an appropriate candidate to be a primary key. This specific issue has been discussed in a prior thread:

https://www.sqlservercentral.com/forums/topic/identity-column-as-primary-key-good-or-bad

In my opinion the viewpoints of Grant Fritchey and roger.plowman are very correct.

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können