.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Special Insert Constraint Rule for Two Columns

Posted By:      Posted Date: October 19, 2010    Points: 0   Category :ASP.Net

I have a SQL Server 2000 table with 7 columns and would like to add a constraint that prevents a row being added with the same value for both Col2 and Col3.

So, for example, if Col2 = 205 and Col3 = 5, then the constraint should prevent inserting another row with the values for Col2 = 205 and Col3 = 5.

The following is an example of what I would like to prevent:

Col1     Col2     Col3    Col4    Col5    Col6

1          205       5        1          1         1

2          205       5        0          0         0

View Complete Post

More Related Resource Links

INSERT statement with OUTPUT clause, referencing outer query columns

I have a problem to solve and I have run into what appears to be a limitation of TSQL.  I have looked around and did not find much on this subject, so I apologize if this duplicates another post.  I am using SQL Server 2008. From what I have read on BOL, when you are performing a DELETE or UPDATE statement, you can reference unaffected columns from the outer query in the OUTPUT clause, but this is apparently not allowed in an INSERT statement. I am working on a process that will create new copies of existing records - essentially, the user can create a whole new copy of a set of records, and the process requires that I track both what the original PK values were and the corresponding PK values for the new rows. This example will hopefully spell out my problem.  This script shows two tables, [Primary_Object] and [Secondary_Object].  Not shown here are multiple tables that rely on [Secondary_Table], which is why I have to be able to track this info. This first script shows the setup of the tables involved and the data involved: /* create test data */ create table primary_object ( primary_object_id int identity(1,1), parent_object_id int, name char(1)) create table secondary_object ( secondary_object_id int identity, primary_object_id int, amount money) insert into primary_object (parent_object_id, name) select 0, 'A' insert into secon

Insert the results of a stored procedure into specific columns

Hello, I'm trying to update a table with values from a stored procedure. Here is what I've got: Open My_Cursor DECLARE @username varchar(200), @add1 varchar(200), @add2 varchar(200), @city varchar(200), @st varchar(200), @zip varchar(200) Fetch NEXT FROM MY_Cursor INTO @username WHILE @@FETCH_STATUS = 0 BEGIN insert into sheet1$ (f19,f20,f21,f22,f23) EXEC [dbo].[GetPrimaryEEsAddress] @dependentUsername = @username WHERE username = @username FETCH NEXT FROM MY_Cursor INTO @username END Getting error: Msg 156, Level 15, State 1, Line 11 Incorrect syntax near the keyword 'WHERE'.

Insert columns into GridView and rendercontrol error

Hi I´m inserting columns into a gridview at run time using: gvRageReport.Columns.Insert(index, column) When I render the gridview in orther to export to excel all the excel columns are empty except the new insert columns, but when I add the new columns using gvRageReport.Columns.Add(column) the excell is exported successfully.Any idea?

Violation of PRIMARY KEY constraint 'PK_User_Details'. Cannot insert duplicate key in object 'dbo

hi,i'm using sql server 2005 as backend for web development .In one of my database table User_Details, 'user_id' is the primary key ,and it is set to autoincrementing as follows.object count = objUser.countUid(); string count1 = Convert.ToString(count);            if (count1 == "0")            {                string uid5 = "U1";           objUser.addUser(string uid5);}else{               object uid = objUser.selectUid();                string usid = uid.ToString();                string uid2 = usid.Substring(1);                int uid3 = int.Parse(uid2);                uid3 += 1;                string uid4 = usid.Substring(0, 1);          

Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object


i am unable to insert values into my table when i am using primary key and foreign key in my tables i am sure that i have not voilated the constraints rules and its working well when i remove the constraints.....

display / insert values by comparing 2 columns of a table



i have a table which has the following columns 

A_ID, A_Value,A_Date,B_ID,B_Value_B_Date


i have another table as follows

A_ID,B_ID,A_Value,B_Value, Date


this is to be done 

* if A_date and B date are equal, one record shoould be entered to the TABLE 02. the values should be A_ID,B_ID,A_Value_B_Value and A_Date (since the date is equal, no problem; even can insert B_Date instead of A_Date)

* if A_Date and B_Date are different, then the following should happen

insert one record to TABLE02 as A_ID,B_ID, A_Value, 0 , A_Date

insert another record to TABLE02 as A_ID,B_ID, 0 , B_Value, B_Date


(As you can see, when the date differs we enter the single record of Table 01 into 2 recoerds in table 02. the Value column and Date column are related in such a way that when A's date is entered, the value for B should be 0 and vise versa..)


i tried having a UDF but in halfway remembered that we cannot include INSERTS in UDFs..

can anyone please explain me how can i solve this problem ??


PS: For your reference , i've sketched a diagram. please click on this link to view it for further clarifications



thanks in advance

-novicedba :)

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tblLinks_tblWebSite".


How does this happen? the tblLinks doesn't even have a column called websiteID.  websiteID is the primary key of the table tblwebsite. LinkID is the primary key of tblLinks. There is not LinkID column in tblwebsite.

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tblLinks_tblWebSite". The conflict occurred in database "Linkexchanger", table "dbo.tblWebSite", column 'WebSiteID'. The statement has been terminated.


So how does this happen? How do I terminate the foreign key relationship? I understand I can add this to my Insert stored procedure:

DROP CONSTRAINT FK_tblLinks_tblWebSite

but that stored procedure is used for other functions in my website, and I don't know if doing that is a good idea. Is there a way to relax the constraints for the purpose of this function here?  I'm taking data from a backup links table and trying to write a new row in the links table.

INSERT statement conflicted with COLUMN FOREIGN KEY constraint...


Hi there,

I have a stored procedure which i pass a number of parameters into. One of these parameters is staffNo (only passed this in because i couldn't execute the query without it). The thing is this field can be Null, but when trying to pass null into it it comes up with an Foreign Key conflict. staffNo is a foreign key within the table i'm inserting the data into.

This is the error i get:

"INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'PropStaffFK'. The conflict occurred in database 'DewMountain', table 'TblStaff', column 'staffNo'. The statement has been terminated. The 'PropertyAdvert' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead. "


Does anyone know of away around this? how to pass a null value to the stored procedure without it causing this error.

Thank you


'concatenating' several columns together for Insert into an XML column



I am inserting a row into a Table. One of the columns in this table is of XML datatype.

I am using a Select statement to provide the values that will be inserted into the row. However, I would like to combine some of these values and parse them into XML for insertion into the XML column.


An example might help:


The Table to insert into has the following columns:

Table name: myTable

ID     int

Data1     string

Data2     string

Data3     string

DataXml     xml


I am inserting into this table using the following:


Code Snippet

insert into myTable

(Data1, Data2, Data3, DataXml)

select Data1, Data2,

Validation for composite key columns in insert.aspx


hey all,

I have a table like the one given below in my database:

CREATE TABLE [dbo].[MyTable](
    [ColPK] [int] IDENTITY(1,1) NOT NULL,
    [Col1] [int] NOT NULL,
    [Col2] [int] NOT NULL,
    [Col1] ASC,
    [Col2] ASC

I am working Dynamic data web application and using the insert.aspx page template to insert data into this table.

If a user enters the values 2 & 2 for Col1 and Col2 fields respectively, and clicks on insert hyperlink it gets saved. Now again if user tries to enter the same values, and clicks on insert hyperlink, nothing happens on the webpage. Internally, I know that a database exception has occured because these we cannot repeat the same values for col1 and col2.

I wanted to display a user message in such scenario so that user understands that already such a combination exists in database. Is ther a way in Dynamic data through which I can handle this?

If no, then what is the wrokaround?

Thanks in advance...

special characters insert



i have just listed ascii table in sql server 2005 trying to find character '½ ' and couldn't find it. its unicode code is 189.

is it possible to insert for example '37 ½ ' into table ?

insert statement violated because Foreign key constraint


I  create an SSIS package, i use data flow to import rows from different server to local server. Unfortunately, there always be error message something about insert statement violated because of foreign key "The INSERT statement conflicted with the FOREIGN KEY constraint" since the table is transactional table that has some foreign key. I want to minimize using temporary table and trying to use toolbox item in ssis.I would appreciate any since i am ssis newbie.


How to insert data into CLOB columns in DB2 from sql server ssis packages



Could any one let me know how to insert values into CLOB from sql server 2005 to DB2.  Its as emergency

Thanks & Regards,Sridhar

"Violation of PRIMARY KEY constraint . Cannot insert duplicate key in object ".

How this error could arise? I use data flow to insert rows from another server. I checked there is no duplicate key in primary key. thanks

How to insert or update XML columns

 --Works ok to update XML by explicitly entering XML, or one-by-one:    
declare @XMLNODE table (id int identity, doc xml)
    insert @XMLNODE (doc) values ('<Root><Elements><Items><OldItem><ID>1</ID><Show Pointer="yes" /><Display Pointer="Display"><Detail1>some Details</Detail1></DisplayDetails></OldItem></Items>      </Elements></Root>'  )
  --This is just an XML variable to get the output, but not related to the ID in table:      

Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object...


Hi all I set up transactional replication between a sql 2008 and sql 2005 server.  I have a table with an identiy column and a varchar field. I accidentally SET IDENTITY INSERT ON at the subscriber instead of the subscriber server and inserted a row. After that every row I inserted in the publisher has not be replicated to subscriber? I see this error message in the replication monitor?

Violation of PRIMARY KEY constraint 'PK__IdentTes__32149A135070F446'. Cannot insert duplicate key in object 'dbo.IdentTest'


I tried deleting the row at subscriber but still the same message?


MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend