aiDeeva.com

Author: Jugal Shah

  • Error: The INSERT statement conflicted with the FOREIGN KEY constraint

    Error Description
    Msg 547, Level 16, State 0, Line 1
    The INSERT statement conflicted with the FOREIGN KEY constraint “foreignKey_child”. The conflict occurred in database “jugal_1”, table “dbo.parent”, column ‘parentid’.
    The statement has been terminated.

    Solution: Error message is pretty clear, it means the value you are inserting into child table is not exist in the parent table. There could be number of reason for i.e value not exist in parnet table, you have added value in the table but yet it is not commited, you are updating the foreign key column.

    Script to reproduce issue
    create table parent
    (
    parentid int constraint primaryKey_parent primary key,
    name varchar(10)
    )

    create table child
    (
    childid int constraint foreignKey_child foreign key references parent(parentid),
    name varchar(10)
    )

    insert into child values(10,’jugal’)

  • Script to list out all DMVs and DMFs of SQL Server


    -- To check the diffrent kind of system objects
    SELECT Distinct type_desc
    FROM sys.system_objects

    -- To get the list of DMVs or DMFs
    SELECT name, type, type_desc,SCHEMA_NAME(schema_id) as SNAME
    FROM sys.system_objects
    WHERE name LIKE 'dm[_]%'
    ORDER BY name

  • How to generate script using SSMS GUI?

    You can generate script of GUI functionality by clicking below high-lighted option in the image.

    It will give you all the below options.

  • How to rename SA account in SQL Server

    You can use below script to rename SA account. Script will work with SQL Server 2005 and higer versions.

     ALTER LOGIN sa Disable;
    ALTER LOGIN sa WITH NAME = [Jugal];

  • New DMF for SQL Server 2008 sys.dm_fts_parser to parse a string

    Check my new article on MSSQLTips

    http://www.mssqltips.com/tip.asp?tip=2175