There is an interesting factoid you should know when it comes to working with foreign key constraints in SQL Server.
Normally, foreign key constraints are linked to a Primary Key column in a parent table
Take a look at the following Books and BookSales tables:
CREATE TABLE Books ( BookID INT IDENTITY(10,5) PRIMARY KEY, Title VARCHAR(35), Author VARCHAR(15), Pages INT ) CREATE TABLE BookSales ( SaleID INT IDENTITY(100,1), BookNumber INT, CustNumber INT, OrderDate DATETIME )
The BookSales table is meant to store details about the orders that have been placed for our bookstore.
Notice the BookNumber column in that table. This column is meant to be a foreign link back to the Books table to tell us what book was purchased.
The values in the BookNumber column are meant to link back to values in the BookID column in the Books table, which is the Primary Key of that table.
Since the parent column is a Primary Key, we can create an actual foreign key constraint in the child table on the BookNumber column:
ALTER TABLE BookSales ADD CONSTRAINT fk_BookNum FOREIGN KEY(BookNumber) REFERENCES Books(BookID)
This creates a situation where an ID value must first exist in the Books table before it can be referenced in the BookSales table.
Factoid: A foreign key constraint could be linked to a column with a unique constraint instead
Folks, the factoid you should know is that you could link your foreign column to a parent column that has a unique constraint instead of a Primary Key constraint.
Take the following Customers table as an example, where a unique constraint is placed on the CustomerID column:
CREATE TABLE Customers ( CustomerID INT UNIQUE, FirstName VARCHAR(20), LastName VARCHAR(30) )
This CustomerID column could certainly be set as the parent column in a foreign key constraint:
ALTER TABLE BookSales ADD CONSTRAINT fk_CustomerNum FOREIGN KEY(CustNumber) REFERENCES Customers(CustomerID)
We can insert rows into our tables successfully, making sure to maintain referential integrity:
We get an error message if we try to insert a CustNumber value in the sales table that doesn’t exist in the Customers table:
So easy!
Next Steps:
Leave a comment if you found this tutorial helpful!
Foreign key constraints are excellent for maintaining the referential integrity of the data in your database. You should definitely understand how they work. Check out the full beginner-friendly tutorial to learn more:
SQL Server Foreign Key: Everything you need to know
Also, if you want to learn about all the constraints available to us in SQL Server, get your hands on this FREE EBOOK:
FREE Ebook on SQL Server Constraints!
This FREE Ebook contains absolutely everything you need to know about all the different constraints available to us in Microsoft SQL Server, including:
- Primary Key constraints
- Foreign Key constraints
- Default constraints
- Check constraints
- Unique constraints
Everything found in this tutorial is thoroughly discussed in the Ebook. This Ebook will definitely be a great resource for you to keep and reference throughout your career as a data professional. Get it today!
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
If you have any questions, or if you are struggling with a different topic related to SQL Server, I’d be happy to discuss it. Leave a comment or visit my contact page and send me an email!
