A flexible foreign key

问题:

I have a sql server database. I'm developing a vb.net application.

Now I'm thinking to have an "Event" table that should keep all the events related with my database. But one of the fields of this table should be a field ObjectID that is related with the record that this event has to do. But this record may be on different tables. For example :

• Event 1 ---- Record 25 on table Clients
• Event 2 ---- Record 30 Table Invoices
• Event 3 ---- Record 40 Table Articles ...

The problem is that this field ObjectID should be a Foreign key in a flexible way , because may be related with different tables.

Is there any way I can resolve this case ?

Thank you !

回答1:

One way to solve it would be to add a table to your database to act like a base for the other tables and connet it with a one to one relationship to the other tables, and then connect the events table to this base table.
This will allow you to keep data integrity for each of the tables.
The base table can be as simple as just one column, or can have columns thay all other tables have in common, thus implementing a sort of "inheritance" in your data structure.

Create the base table (assuming no common columns between other tables):

CREATE TABLE TblObjectBase 
(
    ObjectBase_Id int IDENTITY(1,1) PRIMARY KEY
)

Then, for any other table that needs to be referenced by the ObjectId in the Events table:

CREATE TABLE TblClients 
(
    Client_Id int PRIMARY KEY,
    Client_FirstName varchar(10),
    Client_LastName varchar(10),
    --  Other client related data
    CONSTRAINT FK_TblClients_TblObjectBase
               FOREIGN KEY(Client_Id) 
               REFERENCES TblObjectBase(ObjectBase_Id)
)

CREATE TABLE TblInvoices
(
    Invoice_Id int PRIMARY KEY,
    -- other incoice related data
     CONSTRAINT FK_TblInvoices_TblObjectBase
               FOREIGN KEY(Invoice_Id) 
               REFERENCES TblObjectBase(ObjectBase_Id)
)

The only thing remaining is to insert a new value to the TblObjectBase for any insert on your other tables. This can be be easily achived by either stored procedures or instead of insert triggers.
An insert procedure could look like this:

CREATE PROCEDURE Insert_TblClients
(
    @Client_FirstName varchar(10),
    @Client_LastName varchar(10),
    -- any other client related data you might have
)
AS
DECLARE @ClientId int

-- Insert a new record to the base table:
INSERT INTO TblObjectBase DEFAULT VALUES;

-- Get the id you've just inserted:
SELECT @ClientId = SCOPE_IDENTITY();

-- Insert the data to the clients table:
INSERT INTO TblClients 
(Client_Id, Client_FirstName, Client_LastName.....) VALUES
(@ClientId, @Client_FirstName, @Client_LastName...)

An instead of insert trigger would look like this:

CREATE TRIGGER TblClients_IO_Insert ON TblClients INSTEAD OF INSERT 
AS
BEGIN

DECLARE @ClientId int

-- Insert a new record to the base table:
INSERT INTO TblObjectBase DEFAULT VALUES;

-- Get the id you've just inserted:
SELECT @ClientId = SCOPE_IDENTITY();

INSERT INTO TblClients 
(Client_Id, Client_FirstName, Client_LastName.....) 
SELECT @ClientId, Client_FirstName, Client_LastName..... 
FROM inserted

END

If you choose to go with the instead of insert, the fact that the Identity value comes from another table should be transparent to the client (your vb.net program).

回答2:

SQL Server doesn't support creating such constraint.

But you can simulate the link programmatically without much trouble.

CREATE TABLE tbl_Event
(
    [idEvent]       INT IDENTITY(1,1) NOT NULL,
    [TableSource]   INT NOT NULL,
    [SourceId]      INT NOT NULL
    --Events fields
    CONSTRAINT [PK_Tests] PRIMARY KEY CLUSTERED 
    (
        [id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

In the above exemple, SourceId is the foreign key
TableSource is used to know which table the foreign key is from.
In table source you could use sys.objects.object_id of the table.
However, since you don't have much control on those keys that are managed by SQL, I recommend using you own table with defined constant for each table instead of sys.objects.
This way you also have more control on which table can have foreign key in this table and it become really handy overtime.

CREATE TABLE tbl_tableSource(
    [idTableSource] INT IDENTITY(1,1) NOT NULL,
    [Constant]      INT NOT NULL,
    [Name]          NVARCHAR(255) NULL
 CONSTRAINT [PK_Tests] PRIMARY KEY CLUSTERED 
(
    [idTableSource] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO tbl_tableSource
VALUES(1000, 'tbl_SomeTable')

INSERT INTO tbl_tableSource
VALUES(2000, 'tbl_SomeOtherTable')

Also, this kind of relation, is less good for performance then standard one. So the field type of your keys and of the constant is really important. It should not be heavy. This because you will need to create an index on tbl_event.

To simulate cascade delete ON, you need to implement trigger on parent table like this :

CREATE TRIGGER OneParentTableOnDelete ON  tbl_SomeTable
FOR DELETE
AS 
BEGIN

    DELETE tbl_Event
    FROM DELETED
    INNER JOIN tbl_Event ON tbl_Event.TableSource = [Constant for tbl_SomeTable]
                         AND tbl_Event.idSource = DELETED.id --PrimaryKey


END

To retrieve data, you can then do like this

--For events about one foreign key
SELECT *
FROM tbl_event
WHERE tbl_Event.TableSource = [Constant for tbl_SomeTable]
AND tbl_Event.idSource = @idPrimareyKeyOfSomeTable

--Fore events about multiple foreign key
SELECT *
FROM [tbl_SomeTable]
INNER JOIN tbl_event ON tbl_Event.TableSource = [Constant for tbl_SomeTable]
                      AND tbl_Event.idSource = [tbl_SomeTable].id --PrimaryKey

回答3:

You could create an 'INDEXED VIEW' -- define it however you like to pull the objectIDs + any other relevant fields, as you've described, from record 25 of the clients table, record record 30 of the invoices table, and so on.

You could do the same thing with a stored procedure.

Depending on how you intend to consume the data -- if you're just pulling it into a vb.net datatable, either of these options should work fine.

A benefit to an approach like this is that you don't have to create or maintain any new tables in your database.