PK, FK and other animals...

[Former Member]

As one may recall, there are 2 ways to define foreign key constraints in ASE:

Traditional one:

1.  Create unique index.

2.  Mark it as PK with sp_primarykey

3,  Add Foreign key on the referencing table using sp_foreignkey

4.  Check with sp_helpkey

Old-fashioned one:

1.  Create unique index

2.  Add foreign key using alter table add constraint ... foreign key

3.  Check with sp_helpconstraint

There is a funny inconsistency in the way the two approaches are functioning.

The traditional one is completely de-coupled from indexing a table.  You can define your PK/FK just on anything you want.  Drop any index you want.  No one really cares if PK/FK is supported by any index.  The old-fashioned one is more restrictive:  you cannot define FK constraint if the primary table has no index to support it and you cannot drop that index until the foreign key constraint is cleared.

I wonder why this inconsistency?  Isn't foreign key CONSTRAINT a foreign key?  Whoever uses the traditional approach may mess up his DB to the extend that performance is compromised completely!  It is quite a shame that foreign key does not force both supporting index on primary table AND supporting index on referencing table.  It is quite surprising that there are different ways defining foreign keys - one being completely promiscuous. 

It is possible to define foreign key using BOTH techniques at once:  the traditional one - to have sundry DB tools see the logical relationships in DB, the old-fashioned one - to have the logical relationships at least a partial physical support in the DB.

To me, the problem with the traditional PK/FK approach is that over time physical layout changes (in response to application demands) and the PK gets out of sync with the key that once supported it (drop/create).  With constraint-type FK this may never happen.  Drop index is blocked.

Things to think about...

ATM.