Basic SQL Question

  • porterdl

    SSC Journeyman

    Points: 75

    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)
    );

     

  • pietlinden

    SSC Guru

    Points: 62767

    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 1 month, 4 weeks ago by  pietlinden.
  • porterdl

    SSC Journeyman

    Points: 75

    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 1 month, 4 weeks ago by  porterdl.
  • pietlinden

    SSC Guru

    Points: 62767

    "[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

    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: 62767

    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: 996453

    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)
    Forum FAQ

  • porterdl

    SSC Journeyman

    Points: 75

    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: 996453

    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)
    Forum FAQ

  • pietlinden

    SSC Guru

    Points: 62767

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

    LOL

  • Jeff Moden

    SSC Guru

    Points: 996453

    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)
    Forum FAQ

  • scdecade

    SSC Eights!

    Points: 807

    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.

     

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

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