CODEWITHSUNDEEP
sqlsql-server
Tester101
Tester101

Reputation: 8172

How should I handle multiple foreign keys that all point to the same column in another table?

So let's say I have these two tables...

IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='UnitsDef' AND xtype='U')
CREATE TABLE UnitsDef
(
    UnitsID INTEGER PRIMARY KEY,
    UnitsName NVARCHAR(32) NOT NULL,
    UnitsDisplay NVARCHAR(8) NOT NULL
);

IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='Dimensions' AND xtype='U')
CREATE TABLE Dimensions
(
    DimID INTEGER PRIMARY KEY IDENTITY(0,1),
    DimX FLOAT,
    DimXUnitsID INTEGER DEFAULT 0,
    DimY FLOAT,
    DimYUnitsID INTEGER DEFAULT 0,
    DimZ FLOAT,
    DimZUnitsID INTEGER DEFAULT 0,
    FOREIGN KEY (DimXUnitsID) REFERENCES UnitsDef(UnitsID), 
    FOREIGN KEY (DimYUnitsID) REFERENCES UnitsDef(UnitsID),
    FOREIGN KEY (DimZUnitsID) REFERENCES UnitsDef(UnitsID)
);

I'll insert data into the first table similar to this...

INSERT INTO UnitsDef (UnitsID, UnitsName, UnitsDisplay) VALUES (0, 'inch', 'in.');
INSERT INTO UnitsDef (UnitsID, UnitsName, UnitsDisplay) VALUES (1, 'millimeter', 'mm');
INSERT INTO UnitsDef (UnitsID, UnitsName, UnitsDisplay) VALUES (2, 'degree', '°');

Am I going about this the right way? This is a simplified version of the problem, but I need to know which unit each measurement is given in. Is there a better design practice for this type of situation?

How would I handle the ON DELETE and ON UPDATE for these foreign keys? If I try to cascade deletes and updates, SQL Server would not be so happy about that.

Upvotes: 0

Views: 35

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Your method is pretty good. I would make the suggestion right off that UnitsId be an identity column, so it gets incremented. Your inserts would then be:

INSERT INTO UnitsDef (UnitsName, UnitsDisplay) VALUES ('inch', 'in.');
INSERT INTO UnitsDef (UnitsName, UnitsDisplay) VALUES ('millimeter', 'mm');
INSERT INTO UnitsDef (UnitsName, UnitsDisplay) VALUES ('degree', '°');

You should also make the string columns unique in UnitsDef and give them case-sensitive collations. After all, Ml and ml are two different things ("M" is mega and "m" is milli).

You might also want to combine the units and values into a single type. This has positives and negatives. For me it adds overhead, but it can help if you want to support a fuller algebra of types.

Upvotes: 1

Related Questions