Triggers vs. Foreign-keys?
-
(This may have been asked before, and if so, please just point me to the right place.)
Folks,
I have a new collegue who insists that foreign-keys are evil, and are a major source of deadlocks. He says that building triggers in all the places where referencial data integrity is needed is the best solution.
This seems to me *very* unlikely. If foreign-keys were inferior to triggers, imagine there would be an immense number of articles on sources like SqlServerCentral.com trumpeting that fact. Foreign-keys are fundamental to all relational DB systems, not just MSSQL -- why have them if they are inferior? So, I need to either find out he's right (unlikely), or find out how to present to him the facts so that he doesn't go making insane redesigns to our current system.
First: Is there any merit to what he says? Can straight-forward FKs cause timeouts? How significant is the timeout risk? How best to mitigate this issue?
Second: If there is NO merit to what he says, can anyone suggest some simple demos to illustrate that fact to him?
- Tom
-
Here is an excellent article from DBMS Online that will validate your argument
http://www.dbmsmag.com/9606d17.html
I'm with you. I find triggers to be too loose for enforcing true referential integrity and really defies true relational database design as stipulated in SQL standards (i.e., ANSI SQL-92).
All the best,
DaleAuthor: An Introduction to SQL Server 2005 Management Studio
-
I like the theory of fkeys, but in practice I rarely define them. I work in a fairly fluid environment that is also replicated, removing the fkeys makes it easier to make changes when I need to. Cart before the horse? Perhaps. Whether or not an fkey causes slowness or deadlocks is going to be a function of the speed of the db. No index or bad indexes, it's going to hurt. For max speed I prefer to code the integrity into the procs instead of using triggers in most cases, that way it only costs when it matters.
Hows that for not quite by the book?!
Andy
-
My thoughts:
Triggers = Psychic Prediction
Foreign Keys = True Referential Integrity
Whatever happened to those ERDs (for Frank, Entity Relationship Diagrams) and UML (Universal Modeling Language) Class Diagrams? Do they just get thrown out the window in the physical DB Design phase? Here's a direct quote from the Oracle8 Server Application Developer's Guide
Release 8.0,
http://www.rmitbit.net/bk861/ora8AppDevGuide/ch9.htm
quote:
Designing TriggersUse the following guidelines when designing triggers:
* Use triggers to guarantee that when a specific operation is performed, related actions are performed.
* Use database triggers only for centralized, global operations that should be fired for the triggering statement, regardless of which user or database application issues the statement.
* Do not define triggers that duplicate the functionality already built into Oracle. For example, do not define triggers to enforce data integrity rules that can be easily enforced using declarative integrity constraints.
* Limit the size of triggers (60 lines or fewer is a good guideline). If the logic for your trigger requires much more than 60 lines of PL/SQL code, it is better to include most of the code in a stored procedure, and call the procedure from the trigger.
* Be careful not to create recursive triggers. For example, creating an AFTER UPDATE statement trigger on the EMP table that itself issues an UPDATE statement on EMP causes the trigger to fire recursively until it has run out of memory.
Sorry, I don't agree with using triggers in place of foreign keys. Here's another reference to Oracle8 for enforcing business rules to your DB through integrity constraints (foreign keys), http://www-db.helsinki.fi:8889/doc/server.805/a58241/ch6.htm. I don't know about you but it makes perfect sense to me.
quote:
Enforcing rules with integrity constraints is less costly than enforcing the equivalent rules by issuing SQL statements in your application. The semantics of integrity constraints are very clearly defined, so the internal operations that Oracle performs to enforce them are optimized beneath the level of SQL statements in Oracle. Since your applications use SQL, they cannot achieve this level of optimization.Enforcing business rules with SQL statements can be even more costly in a networked environment because the SQL statements must be transmitted over a network. In such cases, using integrity constraints eliminates the performance overhead incurred by this transmission.
Edited by - DALEC on 06/24/2003 4:22:55 PM
All the best,
DaleAuthor: An Introduction to SQL Server 2005 Management Studio
-
I basic need/purpose is to perform some integrity checks, whether through triggers, foreign keys, stored procedures, etc.
I think one should look at the seq in which the server apply foreign key checks, trigger, etc. which cost the least to achieve the wanted results.
One thing about triggers one can do wonders with it, preventing any app, users with Query Analyser, etc. performing an operation that will result in breaking a business rule or an integrity rule but one should always try and keep transactions as short as possible.
I definitely do not agree with the trigger only solution.
-
You can precisely replicate the functionality of a FK constraint using a trigger. But you will effectively be rewriting Sybase/MS C (or whatever it is) code, using T-SQL. I can't imagine that that could be more efficient.
However, if you want to enforce cascade update/delete operations in SQL 7-, or to enforce integrity across databases, you would need to use a trigger instead of a FK.
While there is of course a performance advantage to removing ref. integrity constraints (to be balanced against the risk of loss of ref. integrity), there is no point at all in simply reproducing the same functionality using a trigger.
The reason for timeouts from FKs is (1) the lock placed on the primary key table to check the data values, and (2) the extra time for which locks may be held on other tables while the integrity is being checked. Both of these will occur (and probably more slowly) when you se a trigger to enforce ref. integrity.
THe only reason I can see for doing this is that there are already triggers on the tables doing something very similar, so that changing them to check integrity would add no extra overhead in terms of table access and almost no difference in speed. That doesn't seem a very likely scenario.
Another drawback of using triggers is that the relationship isn't discoverable by means of system views/tables. This probelm can be circumvented by declaring the foreign key, but then disabling it, using ALTER TABLE <t> NOCHECK CONSTRAINT <c>.
You should ask your colleague for his reasons for preferring triggers.
Tim
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
I'm not sure what you are saying about SQL 7:
quote:
...if you want to enforce cascade update/delete operations in SQL 7-, or to enforce integrity across databases, you would need to use a trigger instead of a FK.But I know this is how it is done in SQL 2000 (and Oracle 8, for that matter):
quote:
From Microsoft MSDN:Cascading Referential Integrity Constraints
Cascading referential integrity constraints allow you to define the actions Microsoft® SQL Server™ 2000 takes when a user attempts to delete or update a key to which existing foreign keys point.
The REFERENCES clauses of the CREATE TABLE and ALTER TABLE statements support ON DELETE and ON UPDATE clauses:
[ ON DELETE { CASCADE | NO ACTION } ]
[ ON UPDATE { CASCADE | NO ACTION } ]
NO ACTION is the default if ON DELETE or ON UPDATE is not specified. NO ACTION specifies the same behavior that occurs in earlier versions of SQL Server.
ON DELETE NO ACTION
Specifies that if an attempt is made to delete a row with a key referenced by foreign keys in existing rows in other tables, an error is raised and the DELETE is rolled back.
ON UPDATE NO ACTION
Specifies that if an attempt is made to update a key value in a row whose key is referenced by foreign keys in existing rows in other tables, an error is raised and the UPDATE is rolled back.
CASCADE allows deletions or updates of key values to cascade through the tables defined to have foreign key relationships that can be traced back to the table on which the modification is performed. CASCADE cannot be specified for any foreign keys or primary keys that have a timestamp column.
ON DELETE CASCADE
Specifies that if an attempt is made to delete a row with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are also deleted. If cascading referential actions have also been defined on the target tables, the specified cascading actions are also taken for the rows deleted from those tables.
ON UPDATE CASCADE
Specifies that if an attempt is made to update a key value in a row, where the key value is referenced by foreign keys in existing rows in other tables, all of the foreign key values are also updated to the new value specified for the key. If cascading referential actions have also been defined on the target tables, the specified cascading actions are also taken for the key values updated in those tables.
Examples of cascading referential actions can be based on the FK_Products_Suppliers constraint on the Products table in Northwind. This constraint establishes a foreign key relationship from the SupplierID column in the Products table to the SupplierID primary key column in the Suppliers table. If ON DELETE CASCADE is specified for the constraint, deleting the row in Suppliers where SupplierID equals 1 also deletes the three rows in Products where SupplierID equals 1. If ON UPDATE CASCADE is specified for the constraint, updating the SupplierID value in the Suppliers table from 1 through 55 also updates the SupplierID values in the three rows in Products whose SupplierID values currently equal 1.
Cascading actions cannot be specified for a table that has an INSTEAD OF UPDATE or INSTEAD OF DELETE trigger. After a cascading action has been defined for a table, an INSTEAD OF UPDATE or INSTEAD OF DELETE trigger cannot be added to it.
Multiple Cascading Actions
Individual DELETE or UPDATE statements can start a series of cascading referential actions. For example, a database contains three tables, TableA, TableB, and TableC. A foreign key in TableB is defined with ON DELETE CASCADE against the primary key in TableA. A foreign key in TableC is defined with ON DELETE CASCADE against the primary key in TableB. If a DELETE statement deletes rows in TableA, the operation also deletes any rows in TableB that have foreign keys matching the deleted primary keys in TableA, and then deletes any rows in TableC that have foreign keys that match the deleted primary keys in TableB.
The series of cascading referential actions triggered by a single DELETE or UPDATE must form a tree containing no circular references. No table can appear more than once in the list of all cascading referential actions that result from the DELETE or UPDATE. The tree of cascading referential actions must not have more than one path to any given table. Any branch of the tree is terminated when it encounters a table for which NO ACTION has been specified or is the default.
Triggers and Cascading Referential Actions
Cascading referential actions fire the AFTER triggers in this sequence:
All of the cascading referential actions directly caused by the original DELETE or UPDATE are performed first.
When the original cascading referential actions have completed, the AFTER triggers on the original table are fired, regardless of whether any rows were updated.
AFTER triggers on tables in the chain of cascaded referential actions are then fired, but only if one or more rows in the table have been updated or deleted.
If any errors are generated by any of the original set of cascading referential actions, an error is raised, no AFTER triggers are fired, and the DELETE or UPDATE is rolled back.
An AFTER trigger can execute a DELETE or UPDATE statement that starts another chain of cascading referential actions. Each secondary chain of referential actions is treated independently. These secondary chains of referential actions behave like the primary chain. All of the secondary referential actions are completed before any secondary triggers are fired. Within each independent unit, there is no defined order in which the cascading referential actions are executed and the affected triggers are fired.
A table that has an INSTEAD OF trigger cannot also have a REFERENCES clause that specifies a cascading action. An AFTER trigger on a table targeted by a cascading action, however, can execute an INSERT, UPDATE, or DELETE statement on another table or view that fires an INSTEAD OF trigger defined on that object.
Cascading Referential Constraints Catalog Information
The following catalog information is available about cascading referential constraints.
The Transact-SQL OBJECTPROPERTY function supports these new values for the property parameter.
Value Object Description
CnstIsDeleteCascade Constraint FOREIGN KEY constraint defined with ON DELETE CASCADE
CnstIsUpdateCascade Constraint FOREIGN KEY constraint defined with ON UPDATE CASCADE
The REFERENTIAL_CONSTRAINTS information schema view returns CASCADE in the UPDATE_RULE or DELETE_RULE column when either ON UPDATE CASCADE or ON DELETE CASCADE is specified. NO ACTION is returned when either ON UPDATE NO ACTION or ON DELETE NO ACTION is specified, or if ON UPDATE or ON DELETE is not specified at all.
The UPDATE_RULE and DELETE_RULE columns returned by sp_fkeys and sp_foreignkeys are set to 0 when CASCADE is specified, and return 1 when NO ACTION is specified or is the default.
When a foreign key is specified as the object of sp_help, the output result set contains these new columns.
Column name Data type Description
delete_action nvarchar(9) Indicates whether the delete action is CASCADE, NO ACTION, or N/A (not applicable).
update_action nvarchar(9) Indicates whether the update action is CASCADE, NO ACTION, or N/A (not applicable).
I think it may be best for some folks to revisit Creating and Maintaining Databases, Designing Databases and Designing Tables on MSDN. If I knew you were trading off triggers for foreign keys as a database developer, I would think twice about hiring you. Seriously.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_04_92ib.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_04_92ib.asp
Edited by - DALEC on 06/25/2003 06:07:14 AM
Edited by - DALEC on 06/25/2003 06:08:22 AM
All the best,
DaleAuthor: An Introduction to SQL Server 2005 Management Studio
-
Here's another one from MSDN (Microsoft Developers Network):
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da_0777.asp
quote:
ConstraintsConstraints allow you to define the way Microsoft® SQL Server™ 2000 automatically enforces the integrity of a database. Constraints define rules regarding the values allowed in columns and are the standard mechanism for enforcing integrity. Using constraints is preferred to using triggers, rules, and defaults. The query optimizer also uses constraint definitions to build high-performance query execution plans.
Classes of Constraints
SQL Server 2000 supports five classes of constraints.
NOT NULL specifies that the column does not accept NULL values.
CHECK constraints enforce domain integrity by limiting the values that can be placed in a column.
A CHECK constraint specifies a Boolean (evaluates to TRUE or FALSE) search condition that is applied to all values entered for the column; all values that do not evaluate to TRUE are rejected. You can specify multiple CHECK constraints for each column. This sample shows the creation of a named constraint, chk_id, that further enforces the domain of the primary key by ensuring that only numbers within a specified range are entered for the key.
CREATE TABLE cust_sample
(
cust_id int PRIMARY KEY,
cust_name char(50),
cust_address char(50),
cust_credit_limit money,
CONSTRAINT chk_id CHECK (cust_id BETWEEN 0 and 10000 )
)
UNIQUE constraints enforce the uniqueness of the values in a set of columns.
No two rows in the table are allowed to have the same not null values for the columns in a UNIQUE constraint. Primary keys also enforce uniqueness, but primary keys do not allow null values. A UNIQUE constraint is preferred over a unique index.
PRIMARY KEY constraints identify the column or set of columns whose values uniquely identify a row in a table.
No two rows in a table can have the same primary key value. You cannot enter a NULL for any column in a primary key. NULL is a special value in databases that represents an unknown value, which is distinct from a blank or 0 value. Using a small, integer column as a primary key is recommended. Each table should have a primary key.
A table may have more than one combination of columns that could uniquely identify the rows in a table; each combination is a candidate key. The database administrator picks one of the candidate keys to be the primary key. For example, in the part_sample table both part_nmbr and part_name could be candidate keys, but only part_nmbr is chosen as a primary key.
CREATE TABLE part_sample
(part_nmbr int PRIMARY KEY,
part_name char(30),
part_weight decimal(6,2),
part_color char(15) )
FOREIGN KEY constraints identify the relationships between tables.
A foreign key in one table points to a candidate key in another table. Foreign keys prevent actions that would leave rows with foreign key values when there are no candidate keys with that value. In the following sample, the order_part table establishes a foreign key referencing the part_sample table defined earlier. Usually, order_part would also have a foreign key against an order table, but this is a simple example.
CREATE TABLE order_part
(order_nmbr int,
part_nmbr int
FOREIGN KEY REFERENCES part_sample(part_nmbr)
ON DELETE NO ACTION,
qty_ordered int)
GO
You cannot insert a row with a foreign key value (except NULL) if there is no candidate key with that value. The ON DELETE clause controls what actions are taken if you attempt to delete a row to which existing foreign keys point. The ON DELETE clause has two options:
NO ACTION specifies that the deletion fails with an error.
CASCADE specifies that all the rows with foreign keys pointing to the deleted row are also deleted.
The ON UPDATE clause defines the actions that are taken if you attempt to update a candidate key value to which existing foreign keys point. It also supports the NO ACTION and CASCADE options.
Column and Table Constraints
Constraints can be column constraints or table constraints:
A column constraint is specified as part of a column definition and applies only to that column (the constraints in the earlier samples are column constraints).
A table constraint is declared independently from a column definition and can apply to more than one column in a table.
Table constraints must be used when more than one column must be included in a constraint.
For example, if a table has two or more columns in the primary key, you must use a table constraint to include both columns in the primary key. Consider a table that records events happening in a computer in a factory. Assume that events of several types can happen at the same time, but that no two events happening at the same time can be of the same type. This can be enforced in the table by including both the type and time columns in a two-column primary key.
CREATE TABLE factory_process
(event_type int,
event_time datetime,
event_site char(50),
event_desc char(1024),
CONSTRAINT event_key PRIMARY KEY (event_type, event_time) )
All the best,
DaleAuthor: An Introduction to SQL Server 2005 Management Studio
-
Hi Dale,
quote:
Whatever happened to those ERDs (for Frank, Entity Relationship Diagrams) and UML (Universal Modeling Language) Class Diagrams?please, don't make me think, I'm the only one who doesn't understand. But I'm glad to see you care
In fact, when it comes to slang or off-oxford english, which is tought in school here, I have my problems. Not with computer acronyms.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
DALEC,
when are you going to post the rest of msdn?
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
DALEK:
quote:
I'm not sure what you are saying about SQL 7:quote:
...if you want to enforce cascade update/delete operations in SQL 7-, or to enforce integrity across databases, you would need to use a trigger instead of a FK.what I'm saying about SQL 7 is that it doesn't have native support for cascade operations, unlike (as we know) SQL 8 and Oracle 8+. It is also not possible to declare refernertial intergity constraint between different databases.
But both these can be done using triggers.
Tim
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Hey gang, the guy needs documentation, I'm just giving him what he asked for. If you have some documentation to support triggers over foreign keys as opposed to what is outlined by MS and Oracle's recommendations for database design, I'm all ears. Otherwise, so far, all you are providing for tkc is opinion and not documented fact.
As for you claim about SQL 7.0, I can't find that anywhere. Can you document that for me?
Edited by - dalec on 06/25/2003 08:50:57 AM
All the best,
DaleAuthor: An Introduction to SQL Server 2005 Management Studio
-
quote:
what I'm saying about SQL 7 is that it doesn't have native support for cascade operations, unlike (as we know) SQL 8 and Oracle 8+. It is also not possible to declare refernertial intergity constraint between different databases.But both these can be done using triggers.
Okay, I found this concerning SQL 7.0 vs. 2000 (and you were right about cascades):
quote:
Cascading Referential Integrity Constraints: There are new ON DELETE and ON UPDATE clauses in the REFERENCES clause of the CREATE TABLE and ALTER TABLE statements.The ON DELETE clause controls what actions are taken if you attempt to delete a row to which existing foreign keys point. The ON UPDATE clause defines the actions that are taken if you attempt to update a candidate key value to which existing foreign keys point.
The ON DELETE and ON UPDATE clauses have two options:
· NO ACTION
· CASCADE
NO ACTION specifies that the deletion/update fail with an error.
CASCADE specifies that all the rows with foreign keys pointing to the deleted/updated row are also deleted/updated.
All the best,
DaleAuthor: An Introduction to SQL Server 2005 Management Studio
-
So, stax68, maybe this guy is an old SQL 7.0 programmer. You might have hit on the whole problem for tkc.
All the best,
DaleAuthor: An Introduction to SQL Server 2005 Management Studio
-
Okay, I think I just found the perfect article for tkc to present to this fellow about ANSI SQL 92 standards and the differences of referential integrity in the MS SQL versions:
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=8687
Good luck, tkc, I think you have enough to go on now.
Here's a qoute:
quote:
The ANSI SQL-92 standard contains the concept of a referential action. Sometimes, instead of preventing a data-modification operation that would violate a foreign key reference, you might want the database system to perform another, compensating action that allows the modification and still honors the constraint... SQL Server releases before SQL Server 6.0 don't let you declare primary and foreign key relationships in your table definitions. All referential integrity validation must take place through triggers, which you have to code in T-SQL. SQL Server 6.0 introduced constraints, including primary and foreign key constraints, but limits referential actions to NO ACTION. SQL Server 7.0 and 6.5 also offer only the NO ACTION option (without the NO ACTION syntax).SQL Server 2000 has supported both CASCADE and NO ACTION referential actions since beta 1
All the best,
DaleAuthor: An Introduction to SQL Server 2005 Management Studio
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply