April 9, 2012 at 7:39 am
sknox (4/9/2012)
Sean Lange (4/6/2012)
sknox (4/6/2012)
Sean Lange (4/6/2012)
... Basically you can simulate a foreign key via triggers but it is NOT actually a foreign key.NO.
Any column in a table is a foreign key column if it references a column in another table. It is a conceptual property of the column.
I disagree completely. A logical type of key you are describing cannot be a foreign key. The definition of a foreign key indicates that RI must be enforced. If RI is not enforced it is NOT a foreign key.
1. If RI enforcement is a requirement for a foreign key, then why can you in SQL server create a foreign key that does not enforce referential integrity (e.g, ON DELETE NO ACTION ON UPDATE NO ACTION) ?
This is enforcing RI. If you specify NO ACTION (the default in SQL Server), the FK is enforced. You cannot delete or update the referenced column/row if there is row referencing it. Specifying an action, in my opinion, really isn't enforcing RI, it is changing the referencing data. I have never used any of the actions because I believe the application should enforce the business rules by explicitly deleting/updating any referencing data.
As you said at first it must reference a column in another table. That directly contradicts your statement about RI.
2. Referencing a column in another table does not imply enforcement of referential integrity. The key is in the term: "referential integrity" means the integrity of the reference. It is possible to have a reference that does not have its integrity enforced. That does not negate the fact that it is a reference.
In reference to SQL Server the trigger idea is not a key. It is a logical way to enforce RI. A foreign key is an object and has a row in sys.sysobject with an xtype = 'F'.
3. I agree that the trigger is not a key, but a way to enforce RI. A Foreign Key object in SQL server is not a key either. It is a logical way to document a key that exists and potentially to enforce RI on that key.
I think my comment on #1 applies here as well. We seem to differ on what enforcing RI means. In my opinion, enforcing RI means that you do not allow referenced data to be changed/removed when referencing rows exist.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 9, 2012 at 7:47 am
EDIT: Jack was faster than me. I did not see his message until after I posted. Sorry for duplicating some of the things he writes.
sknox (4/9/2012)1. If RI enforcement is a requirement for a foreign key, then why can you in SQL server create a foreign key that does not enforce referential integrity (e.g, ON DELETE NO ACTION ON UPDATE NO ACTION) ?
Have you ever tried what happens if you define a foreign key constraint with those attributes and then try to violate the referential integrity? I'll save you the time: you get an error message and the transaction is rolled back. The referential integrity is enforced.
The ON DELETE NO ACTION and ON UPDATE NO ACTION attributes define only what happens if a violation of the foreign key would be caused by a change in the referenced ("parent") table. The options are NO ACTION, SET NULL, SET DEFAULT, and CASCADE. And here are the meanings:
* ON DELETE NO ACTION and ON UPDATE NO ACTION: Take no corrective action. The violation of the foreign key constraint causes the modification to fail.
* ON DELETE SET NULL and ON UPDATE SET NULL: Try to correct the situation by changing the offending values in the referencing ("child") table to NULL. If the column does not permit nulls, this will fail; otherwise the foreign key is no longer violated and the transaction succeeds.
* ON DELETE SET DEFAULT and ON UPDATE SET DEFAULT: As SET NULL, but now the child rows that would cause a violation are set to their default value. If there is no default value or if the default value also doesn't exist in the parent table, this will fail; otherwise the foreign key is no longer violated and the transaction succeeds.
* ON DELETE CASCADE and ON UPDATE CASCADE: Try to correct the error by "cascading" the change. If a row from the parent is deleted, all rows in the child table that reference that row are deleted as well. If a row from the parent has its key value updated, the same update will be made to all referencing rows in the child table. And again, if the result doesn't violate any (other) constraints, the transaction will succeed; otherwise an error will be raised.
2. Referencing a column in another table does not imply enforcement of referential integrity. The key is in the term: "referential integrity" means the integrity of the reference. It is possible to have a reference that does not have its integrity enforced. That does not negate the fact that it is a reference.
True, but not the way you imply it. It is possible to disable a constraint. Fortunately, this does require permissions that normal database users should never have. So unless your DBA does funny stuff, this should only happen temporarily, e.g. to speed up a data load during off hours.
3. I agree that the trigger is not a key, but a way to enforce RI. A Foreign Key object in SQL server is not a key either. It is a logical way to document a key that exists and potentially to enforce RI on that key.
I agree that a foreign key is not a key (in SQL Server, or in the relational model). It is a constraint, and it does enforce relational integrity - unless your DBA is so incompetent that he or she allows constraints to be and remain disabled.
April 9, 2012 at 9:08 am
My 2 cents... The question is either ambiguous or the answer is wrong. I know that you can't a FK constraint across databases. My interpretation of a FK (not a FK constraint) is the relationship between 2 tables that the data modeler creates when designing the database. It can be enforced by either a constraint or trigger or even in code (Yes, I know this is bad practice so don't get on my case about it!).
What piece of information was the author of the question looking for? It is unclear to me.
April 9, 2012 at 11:31 am
Jack Corbett (4/9/2012)
sknox (4/9/2012)
Sean Lange (4/6/2012)
sknox (4/6/2012)
Sean Lange (4/6/2012)
... Basically you can simulate a foreign key via triggers but it is NOT actually a foreign key.NO.
Any column in a table is a foreign key column if it references a column in another table. It is a conceptual property of the column.
I disagree completely. A logical type of key you are describing cannot be a foreign key. The definition of a foreign key indicates that RI must be enforced. If RI is not enforced it is NOT a foreign key.
1. If RI enforcement is a requirement for a foreign key, then why can you in SQL server create a foreign key that does not enforce referential integrity (e.g, ON DELETE NO ACTION ON UPDATE NO ACTION) ?
This is enforcing RI. If you specify NO ACTION (the default in SQL Server), the FK is enforced. You cannot delete or update the referenced column/row if there is row referencing it. Specifying an action, in my opinion, really isn't enforcing RI, it is changing the referencing data. I have never used any of the actions because I believe the application should enforce the business rules by explicitly deleting/updating any referencing data.
That was a bad example. Let's get down to the key (no pun intended) area of contention: enforcement of RI. Would an object defined by the following statements be a foreign key or not?
ALTER TABLE [dbo].[Roles] WITH NOCHECK ADD CONSTRAINT [FK_Roles_DBs] FOREIGN KEY([InstanceID], [DBID])
REFERENCES [dbo].[DBs] ([InstanceID], [dbid])
GO
ALTER TABLE [dbo].[Roles] NOCHECK CONSTRAINT [FK_Roles_DBs]
GO
April 9, 2012 at 12:22 pm
sknox (4/9/2012)
That was a bad example. Let's get down to the key (no pun intended) area of contention: enforcement of RI. Would an object defined by the following statements be a foreign key or not?
ALTER TABLE [dbo].[Roles] WITH NOCHECK ADD CONSTRAINT [FK_Roles_DBs] FOREIGN KEY([InstanceID], [DBID])
REFERENCES [dbo].[DBs] ([InstanceID], [dbid])
GO
ALTER TABLE [dbo].[Roles] NOCHECK CONSTRAINT [FK_Roles_DBs]
GO
Technically the object FK_Roles_DB is a foreign key constraint but it is not enforcing RI because it is explicitly disabled. This is completely different from specifying or not specifying an ACTION when defining a a foreign key which is where I disagreed with your previous post.
The reason you can disable a FK in SQL Server (or any other constraint) most likely has to do with performance on bulk loads or changes. Enforcement of a FK can kill performance of mass inserts/updates/deletes and in case where you KNOW that the relationships have been handled correctly in a bulk action, then temporarily disabling FK can improve performance by magnitudes. See the connect link I posted earlier in the thread. I wouldn't disable a FK often, but there are reasons for it.
The other thing a FK (even when disabled) does is enable you to easily find relationships between tables that aren't easy to find if you are enforcing RI with a trigger (which can be disabled as well). I can easily query system tables to find relationships defined with FK's and maybe there will be a case where I want to know about the relationship, but don't care if rows can be orphaned so I disable the RI portion of the FK. The object will still exist in the database, but won't be serving that purpose anymore.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 10, 2012 at 12:30 am
Thanks for the question.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 10, 2012 at 2:27 am
I am one of those that (mis)read the question.
You asked "Can a foreign key be created", and the answer to that were a no-brainer as the referenced table/column needs to be available at all times, which cannot be guaranteed on remote tables (in other databases or on other servers).
Muddling stuff with triggers widened the field considerably, as you CAN build something resembling a foreign key constraint using triggers. As others has also pointed out.
But thanks for the question anyhow. It still shows that if in doubt, don't take the info provided at face value, but drill down and find out what were really meant. This is also good advice when somebody comes and asks you to do strange stuff to "his" database. Find out what is beneath the question/request, and get the right solution first time around.
April 10, 2012 at 2:55 am
I got it wrong, my reasoning being that disallowing "yes with triggers" would be grossly unfair to those with English as a second language so it must be the correct answer. It's quite a subtle difference between creating a foreign key, and creating a trigger which has exactly the same effect as a foreign key would have.
April 10, 2012 at 8:56 am
Hugo Kornelis (4/6/2012)
...Yes, a foreign key constraint can be enforced across databases using triggers. If you refer to the actual meaning of the constraint in a logical model...
This was my guess at the questioner's intent (why else include the 'with a trigger' option?)
Sadly it turns out I was 'wrong'.
I shall use a different coin next time.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 12, 2012 at 2:13 am
My coin was also not working correctly on this one.:-D
October 9, 2012 at 1:42 am
+1 🙂
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 11 posts - 31 through 40 (of 40 total)
You must be logged in to reply to this topic. Login to reply