Can you create non-enforced foreign key on existing table in SQL Server?
1
vote
1
answer
77
views
Say I am trying to understand some unkown part of a database. I usually rely a *lot* on foreign keys to understand the relations between the data, but the tables in question don't have any for some reason. And what's even worse, the data are not consistent! There are child records lacking their parent.
Can I create the foreign key on such table, but without actually enforcing it (at least temporarily)? As a comment of sorts.
---
We can use following AI generated demo as an example.
CREATE TABLE Invoice (
InvoiceID INT PRIMARY KEY,
CustomerName NVARCHAR(100) NOT NULL,
InvoiceDate DATE NOT NULL,
TotalAmount DECIMAL(18, 2) NOT NULL
);
CREATE TABLE InvoiceRow (
RowID INT PRIMARY KEY IDENTITY(1,1),
InvoiceID INT NOT NULL,
ProductName NVARCHAR(100) NOT NULL,
Quantity INT NOT NULL,
UnitPrice DECIMAL(18, 2) NOT NULL
);
INSERT INTO Invoice (InvoiceID, CustomerName, InvoiceDate, TotalAmount) VALUES
(1, 'John Doe', '2025-07-20', 150.00);
INSERT INTO InvoiceRow (InvoiceID, ProductName, Quantity, UnitPrice) VALUES
(1, 'Product A', 2, 50.00),
(1, 'Product B', 1, 50.00),
(2, 'Product C', 3, 40.00);
This command fails, because the data are not consistent.
ALTER TABLE InvoiceRow
ADD CONSTRAINT FK_InvoiceRow_Invoice
FOREIGN KEY (InvoiceID)
REFERENCES Invoice(InvoiceID);
[Fiddle](https://dbfiddle.uk/m6Nb0ohj) .
Asked by Yano_of_Queenscastle
(1998 rep)
Jul 25, 2025, 08:14 PM
Last activity: Jul 25, 2025, 10:36 PM
Last activity: Jul 25, 2025, 10:36 PM