Walter Meester
HoogkarspelNetherlands
General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
Jim,
>The bug is that you can create them in the first place visually. Relational definition of a primary key is "A field or fields that absolutely unique value that specifically and umambiguously indentifies a specific record in a table."
Yep... but if you define a table as a collection of tuples (or records) WHICH ARE NOT DELETED there is nothing wrong with a filtered primary key.
>If I have a table that has two records with the same value for the primary key, regardless to whether one of them has a specific value in another field (the delete marker field) then the primary key is not a primary key anymore.
Again.. If you want to ingnore the existance of the deleted records (as they are when the tables is packed) the key CAN be regarded as a primary key.
>Filtered primary indexes are only required when the developer has not dealt with the protection of the primary key value. Using surrogate Primary keys is one way to make the whole issue go away. Filtering candidate indexes on deleted is another issue altogether.
That's a matter of taste. If you allow the user to type in their own 'primary key' like a articleno, and you want to prevent duplicates, you still have to work with a candidate key. If then you would allow the user to delete this particular articleno and reuse it after, you still have to use the filter clause. Then a filtered primary index is not a bad idea.
The real bug is that the ALTER TABLE ... ADD PRIMARY KEY ... TAG ... FOR !DELETED doesn't work, while the help say's it does.
Walter,
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only