Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Primary Key -Foerign Key Expand / Collapse
Posted Tuesday, November 12, 2013 10:14 AM


Group: General Forum Members
Last Login: Yesterday @ 9:48 AM
Points: 406, Visits: 1,219
I am using a table A which has Prodcode as Primary key so there are no null values.
Now there is Table B which has a foreign key - Prodcode but it has Null values.

Shouldn't it have all "not null "values for Table B as it references to Table A.
Post #1513548
Posted Tuesday, November 12, 2013 10:42 AM

SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, December 1, 2016 12:24 PM
Points: 608, Visits: 972
If you are asking if the engine is working correctly it is. You can have nullable FKs. They do not check to see if there is a null column in the referencing table.

If you are asking is this a good practice I would say that it depends on why it is nullable. If null in your system "means" something i.e. an arch table two possible FKs but only one is ever present or missing data that will be added later then it is fine. If it causes issues with the data then I would say that you should consider making it not null and changing the process that inserts the data.

This question has the potential of getting a lot of comments about if Nulls or good or bad. In the end the engine is working as excepted.

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1513560
Posted Tuesday, November 12, 2013 6:24 PM

Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, February 24, 2016 6:28 AM
Points: 3,977, Visits: 6,431
I can give you a real world example that has been simplified that you can look at.

How to Design, Build and Test a Dynamic Search Stored Procedure

There are 2 tables: Customers and Shipments. CustID on the Shipments record is a FK into the Customers table, however it supports a NULL value. This is because, at its initial stage of entry, the Shipments record has not yet been assigned to a specific customer ID. Later, when it is assigned a CustID, that must be for a customer that is on file.

Good or bad practice, it is required in some cases.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Post #1513679
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse