SQL Server Performance

FK Issus

Discussion in 'Getting Started' started by AshishJ2008, Dec 31, 2007.

  1. AshishJ2008 New Member

    I have made one FK on one column of the table. I see lots of constraint around(40 or so) for the same FK Once I execute sp_help 'table name'. This is happening in all the tables of the DB. Could you please tell me the reason.
  2. Luis Martin Moderator

    Welcome to the forum.
    I'm afraid you have to ask to the developer team. Non of us, I presume, can tell you why. Anyway wait for others members opinions.
  3. Adriaan New Member

    Not entirely sure - since SQL maintains statistics on data, I presume those are system indexes to support that?
  4. satya Moderator

    Back to the basics, foreign key in one table points to a primary key in another table. Foreign keys prevent actions that would leave rows with foreign key values when there are no primary keys with that value and it can be used to enforce referential integrity.
    BOL states that:
    Now you can control the actions SQL Server 2000 takes when you attempt to update or delete a key to which existing foreign keys point. You can control it by using the new ON DELETE and ON UPDATE clauses in the REFERENCES clause of the CREATE TABLE and ALTER TABLE statements. For example, in the previous versions of SQL Server if you wanted to do a cascade delete from the referenced table when the appropriate record in the parent table is deleted, you had to create a trigger which executed on delete of the parent table, but now you can simply specify the ON DELETE clause in the REFERENCES clause.

  5. lcerni New Member

    I am not sure how your database looks, but in our database (using SQL Server 2005 SP2) a constraint is a default where the developer has assigned a default value to a column. I am assuming that they do this in case the user forgets to fill in the value in the front end app. It also prevent NULL values for the column.
    This is how our Constraint/Default looks like
    USE [DatabaseName]
    ALTER TABLE [dbo].[TableName] ADD CONSTRAINT [DFT_TableName_ColumnName_Value] DEFAULT ('Value') FOR [ColumnName]
    Don't know if this helps.
  6. AshishJ2008 New Member

    We had a problem in deleting table USR_PRF_MST entries. On diagnosis the reason was the foreign key references exceeded 255, the max foreign references on a single table in SQL server 2005 and query processor couldn’t process it. Observed that it’s creating reference for each FK field for each new entry. I dropped all those (around 655 FK ref) and now am able to truncate USR_PRF_MST. This is the case with most of other tables in Quest. Please let me know why this is happening.
    Thanks in advance.
  7. Adriaan New Member

    Huh? The one reason why you cannot truncate your table USR_PRF_MST is that there are FKs referring to this table. If you drop all FKs, indeed you can truncate the table.
    One table with 655 FK references? Yikes!
  8. MohammedU New Member

    655 FKs are too many and hard to believe....but Quest provides third party tools and these guys write the code in such a way other people don't understnad to copy them....
    If you talk to them they should provide you the cleanup script...

Share This Page