INSERT INTO PRODUCT_SALES (Salesmanid,Productid) VALUES (1,1)
INSERT INTO PRODUCT_SALES (Salesmanid,Productid) VALUES (1,2)
INSERT INTO PRODUCT_SALES (Salesmanid,Productid) VALUES (1,3)
SELECT * FROM PRODUCT_SALES
/* SalesmanID is reference key from Sales Master and ProductID is reference key from Product Master. How should i restrict user through DB with any constraint check, if user tries to enter
INSERT INTO PRODUCT_SALES (Salesmanid,Productid) VALUES (1,2),
It should throw error , if possible user defined message would be more useful.
I am trying to create a check constraint that prevents entry of a date range that overlaps any other date range in the table. The below example script results in the error message: The INSERT statement conflicted with the CHECK constraint "ck_dt_overlap". what I am doing wrong?
CREATE TABLE t1 (d1 date,d2 date) go create function dbo.dt_overlap (@d1 date, @d2 date) RETURNS BIT AS BEGIN IF EXISTS (SELECT 1 from t1 where d1 between @d1 and @d2 or d2 between @d1 and @d2) RETURN 1 RETURN 0 END go alter table t1 add constraint ck_dt_overlap CHECK (dbo.dt_overlap(d1,d2)=0) go insert into t1 values ('2015-01-01','2015-02-01') insert into t1 values ('2015-01-15','2015-02-15')
--BOTH inserts result in the following message: The INSERT statement conflicted with the CHECK constraint "ck_dt_overlap".
I'm wondering if there is some sql I can run to check properties on a table. This would be used to verify things like data types, allow nulls and default values have been set to avoid mistakes. This could be done manually one table and one column at a time, but it would be a lot easier to look at it in the results window.
I have a question about a table with triggers or maybe a check constraint.I have the following create tables:
create table bid( seller char(10) not null, item_nummer numeric(3) not null, )
create table Item( startprice char(5) not null, description char(22) not null, start_date char(10) not null, end_date char(10) not null, seller char(10) not null, item_nummer numeric(3) not null, )
What i'm trying to make is this trigger/constraint: colomn "seller" from table Item will get NULL as long as systemdate is > start_date and end_date, then it will get the value from seller from table bid on the same item_nummer in both table).
--If I pass activityId 3 or 2 or 4 it should return 0 as none of the activity is circular but If I pass 5, 6 or 7 it should return 1 as they have circular reference....
I need a sql qry which will require to find a circular reference in it.....
As in above sample of data ,If I pass activityId 3 or 2 or 4 to qry it should return 0 as none of the activity is circular but If I pass 5, 6 or 7 it should return 1 as they have circular reference....
There is a table exists in a database name tbDatabaseProviders.
I want to alter this table to add a column DatabaseProvider Also I want to add a default constraint so if nothing than 1 should be added.
Remember, the table already exists so when I write the script query then I want to first check the constraint if it exists then I dont want to add the constrait otherwise I will add it.
I was having problem to first add a constraint with the constraint name so I could find the constraint before finding it if it already exists.
I am relatively new to SQL and as a project I have been asked to create the SQL for a simple database to record train details. I want to implement a check constraint which will prevent data from being inserted into a table if the weight of the train is more than the maximum towing weight of the locomotive. FOr instance, I need to add the unladen weight and maximum capacity of each wagon (located in the wagon type table) and compare it against the locomotive maximum pulling weight (the locomotive class table). I have the following SQL but it will not work:
check((select SUM(fwt.unladen_weight+fwt.maximum_payload) from hauls as h,freight_wagon as fw,freight_wagon_type as fwt,train as t where h.freight_wagon_serial_number = fw.freight_wagon_serial_number and fw.freight_wagon_type = fwt.freight_wagon_type and h.train_number = t.train_number) < (select lc.maximum_towing_weight from locomotive_class as lc,locomotive as l,train as t where lc.locomotive_class = l.locomotive_class and l.locomotive_serial_number = t.locomotive_serial_number))
The hauls table is where the constraint has been placed and is the intermediary table between train and freight wagon.
I may not have explained this very well; but in short, i need to compare the sum of two values in one table against a values located in another table...At present I keep getting a message telling me the sub query cannot return more than one row.
One of our Oracle Tables changed and I am wondering if there's any way that I can query all of our Stored Procedures to try and find out if that Oracle Table Name is referenced in any of our SQL Server Stored Procedures OPENQUERY statements?
IF NOT EXISTS (SELECT TOP 1 1 FROM dbo.syscolumns WHERE id = OBJECT_ID(N'dbo.Employee) and name = 'DoNotCall') BEGIN ALTER TABLE [dbo].[Employee] ADD [DoNotCall] bit not null Constraint DoNot_Call_Default DEFAULT 0 IF ( @@ERROR <> 0 ) GOTO QuitWithRollback END
It just takes a LOT of time in SQL Server Management studio. I have to cancel the query and cancelling takes a whole lot time. I am using SQL Server 2008.
I am trying to write a SQL Server query that archives x-days old data from [Archive].[TestToDelete] to [Working].[TestToDelete]table. I want to check that if the records on ArchiveTable do not exist then insert then deleted...which will be converted to a proc later.. archives x-days old data from [Working].[TestToDelete] to [Archive].[TestToDelete] table */Here is the table definition, it is the same for both working and archive tables. There are indexes on: IpAddress, Logdate, Server, User and Sysdate (clustered).
CREATE TABLE [Archive].[TestToDelete]([Server] [varchar](16) NOT NULL,[Logdate] [datetime] NOT NULL,[IpAddress] [varchar](16) NOT NULL,[Login] [varchar](64) NULL,[User] [varchar](64) NULL,[Sysdate] [datetime] NULL,[Request] [text] NULL,[Status] [varchar](64) NULL,[Length] [varchar](128) NULL,[Referer] [varchar](1024) NULL,[Agent] [varchar](1024) NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]/* the syntax, which will be converted to proc is: */SET NOCOUNT ON;DECLARE @Time DATETIME,@R INT,@ErrMessage NVARCHAR(1024)SET @R = 1/* set @Time to one day old data */SET @Time =
Hi, I don't know if this is possible, i believe not, so I'm here to ask the experts if is possible to have a foreign key constraint that references the key of one of two tables. Like this: I have 3 tables: TABLE X, TABLE A and TABLE B Is it possible to the FK on TABLE X refernce the PK of TABLE A OR TABLE B? If yes, how can I do this? If not, I need to have a fourth table, so TABLE X references TABLE A and TABLE Y references TABLE B. Thanks!
I would like to delete a row in a table, but I get the following message :
The DELETE statement conflicted with the REFERENCE constraint "FK_cs_Users". The conflict occurred in database "mytable", table "dbo.cs_UserProfile", column 'UserID'. The statement has been terminated. (0.701 sec)
But I did this :
alter table cs_Users disable trigger ALL
delete from cs_Users where MembershipID = 'xxx'
Thank you very much for any help to get this work !
I have 2 databases in sql server. let us say database1 = db1 and database2 = db2 Now both the databases have a same table called table1 with the same fields.
IF data in db1.table1 is updated then data in db2.table2 should be updated automatically.
There are many ways we can do this. one way is to create a INSERT trigger on db1.table1.
But i would like to avoid trigger Is there something in SQL server where I can just link table1 of db1 to db2 and delete the table1 in db2. That means db2 is using the same table that of db1.
I am construvcting a number of databases, some of which containsensitive data and most of which do not. I am attempting to handle thesecurity issues involved in protecting sensitive data in part byputting it in its own database. If the sensitive data is in a databasecalled d_SensitiveData, and in that database there is a table called't_A' (I know, not very informative, but this is only a triviallysimple example :-), and I have a script running in a database'NotSensitiveData' (i.e. there is a statement at the beginning of thescript "USE NotSensitiveData"), how do I referenece the primary key intable 'A'?I tried a variety of things like:ALTER TABLE t_nsXADD CONSTRAINT FK_PersonID FOREIGN KEY (p_idPerson)REFERENCES SensitiveData.t_A (p_idPerson);The above is, in fact, my latest attempt. Everything I tried hasfailed precisely at the point where I specify a table in a differentdatabase. So what is the trick to refering to a table in one databasewhen using another database?This will be used in a ASP.NET v3 application where one of the things Iwant to do is have the authentication provider be a different databasefrom the one used for the main application data.Does anyone know of an example I can download from the web that doesthe same sort of thing I want to try, with some discussion of securityissues involved (i.e. what I can do to harden the application and dataserver)?NB: I am an application developer, not a DBA nor a systemadministrator.ThanksTed
Here is my issue I am new to 2005 sql server, and am trying to take my old data which is exported to a txt file and import it to tables in sql. The older database is non relational, and I had made several exports for the way I want to build my tables. I built my packages fine and everything is working until I start building relationships. I remove my foreign key and the table with the primary key will get updated for the package again. I need to update the data daily into sql, and once in it will only be update from the package until the database is moved over.
It will run and update with a primary key until I add a foreign key to another database.
Here is my error when running the package when table 2 has a foreign key.
[Execute SQL Task] Error: Executing the query "TRUNCATE TABLE [consumer].[dbo].[Client] " failed with the following error: "Cannot truncate table 'consumer.dbo.Client' because it is being referenced by a FOREIGN KEY constraint.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Hello All, i have 2 files in excel which i am uploading them to a folder which is located in the root directory. then i am importing these two files into a sqldatabase. i needed some help in importing them and i got it from my previous post at http://forums.asp.net/t/1261155.aspx but now i have a problem. the import works perfectly first time but when i do it the second time i am getting an error on of the file (header). to explain clearly, what i am doing is , every time i upload a new file , i am deleting the data from the tables, so the new data can be inserted ( basically trying to achieve overwirting the existing data). now this technique works fine with the Detail table but not with the Header table. i think the reason is the header table has a primary key on OrderID and a relationship does exists between the Header and Detail. now how would i overcome this error. can some one please guide me. I really appreciate it.here is my code: // connection for header file protected OleDbCommand headExcelConnection() { // Connect to the Excel Spreadsheet string headConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("~/imports/headerorder.xls") + ";" + "Extended Properties=Excel 8.0;"; // create your excel connection object using the connection string OleDbConnection headXConn = new OleDbConnection(headConnStr); headXConn.Open(); // use a SQL Select command to retrieve the data from the Excel Spreadsheet // the "table name" is the name of the worksheet within the spreadsheet // in this case, the worksheet name is "Sheet1" and is expressed as: [Sheet1$]
OleDbCommand headCommand = new OleDbCommand("SELECT * FROM [Sheet1$]", headXConn); return headCommand; } // importing header information
protected void BtnImpHeader_Click(object sender, EventArgs e) { PanelUpload.Visible = false; PanelView.Visible = false; PanelImport.Visible = true; LabelImport.Text = ""; // reset to blank // Create a new Adapter OleDbDataAdapter objDataAdapter = new OleDbDataAdapter(); // retrieve the Select command for the Spreadsheet objDataAdapter.SelectCommand = headExcelConnection();
// Create a DataSet DataSet objDataSet = new DataSet(); // Populate the DataSet with the spreadsheet worksheet data objDataAdapter.Fill(objDataSet);
// deleting the exisitng table before copy SqlConnection mycon = new SqlConnection(ConfigurationManager.ConnectionStrings["ImportexcelConnectionString"].ConnectionString); SqlCommand SqlCmd = null; mycon.Open(); SqlCmd = mycon.CreateCommand(); SqlCmd.CommandText = "DELETE FROM Header"; ---- showing error over on second time SqlCmd.ExecuteNonQuery(); mycon.Close(); // entering the newer header information
protected OleDbCommand detExcelConnection() { // Connect to the Excel Spreadsheet string detConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("~/imports/detailorder.xls") + ";" + "Extended Properties=Excel 8.0;"; // create your excel connection object using the connection string OleDbConnection detXConn = new OleDbConnection(detConnStr); detXConn.Open(); // create your excel connection object using the connection string // use a SQL Select command to retrieve the data from the Excel Spreadsheet // the "table name" is the name of the worksheet within the spreadsheet // in this case, the worksheet name is "Sheet1" and is expressed as: [Sheet1$] OleDbCommand detCommand = new OleDbCommand("SELECT * FROM [Sheet1$]", detXConn); return detCommand; }// importing detail information
protected void ButtonImport_Click(object sender, EventArgs e) { PanelUpload.Visible = false; PanelView.Visible = false; PanelImport.Visible = true; LabelImport.Text = ""; // reset to blank // Create a new Adapter OleDbDataAdapter objDataAdapter = new OleDbDataAdapter(); // retrieve the Select command for the Spreadsheet objDataAdapter.SelectCommand = detExcelConnection();
// Create a DataSet DataSet objDataSet = new DataSet(); // Populate the DataSet with the spreadsheet worksheet data objDataAdapter.Fill(objDataSet);
// deleting the exisitng table before copy SqlConnection mycon = new SqlConnection(ConfigurationManager.ConnectionStrings["ImportexcelConnectionString"].ConnectionString); SqlCommand SqlCmd = null; mycon.Open(); SqlCmd = mycon.CreateCommand(); SqlCmd.CommandText = "DELETE FROM Detail"; SqlCmd.ExecuteNonQuery(); mycon.Close(); // entering newer detail information SqlConnection mysqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["ImportexcelConnectionString"].ConnectionString); mysqlcon.Open(); foreach (DataRow dr1 in objDataSet.Tables[0].Rows) { String sqlinsert = "insert into Detail values(@param1,@param2,@param3,@param4,@param5,@param6,@param7,@param8,@param9,@param10,@param11,@param12,@param13)"; SqlCommand cmd = new SqlCommand(sqlinsert, mysqlcon); cmd.Parameters.AddWithValue("@param1", dr1[0].ToString()); cmd.Parameters.AddWithValue("@param2", dr1[1].ToString()); cmd.Parameters.AddWithValue("@param3", dr1[2].ToString()); cmd.Parameters.AddWithValue("@param4", dr1[3].ToString()); cmd.Parameters.AddWithValue("@param5", dr1[4].ToString()); cmd.Parameters.AddWithValue("@param6", dr1[5].ToString()); cmd.Parameters.AddWithValue("@param7", dr1[6].ToString()); cmd.Parameters.AddWithValue("@param8", Convert.ToDecimal(dr1[7].ToString())); cmd.Parameters.AddWithValue("@param9", Convert.ToDecimal(dr1[8].ToString())); cmd.Parameters.AddWithValue("@param10", dr1[9].ToString()); cmd.Parameters.AddWithValue("@param11", dr1[10].ToString()); cmd.Parameters.AddWithValue("@param12", dr1[11].ToString()); cmd.Parameters.AddWithValue("@param13", dr1[12].ToString()); cmd.ExecuteNonQuery(); LabelImport.Text = "Rows Inserted"; } mysqlcon.Close(); } the error is as follows: Server Error in '/WebSite6' Application.
The DELETE statement conflicted with the REFERENCE constraint "FK_Detail_Header". The conflict occurred in database "C:DOCUMENTS AND SETTINGSMEMY DOCUMENTSVISUAL STUDIO 2005WEBSITESWEBSITE6APP_DATADATABASE.MDF", table "dbo.Detail", column 'OrderID'.The statement has been terminated. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: The DELETE statement conflicted with the REFERENCE constraint "FK_Detail_Header". The conflict occurred in database "C:DOCUMENTS AND SETTINGSMEMY DOCUMENTSVISUAL STUDIO 2005WEBSITESWEBSITE6APP_DATADATABASE.MDF", table "dbo.Detail", column 'OrderID'.The statement has been terminated.Source Error:
Line 176: SqlCmd = mycon.CreateCommand();Line 177: SqlCmd.CommandText = "DELETE FROM Header";Line 178: SqlCmd.ExecuteNonQuery();Line 179: mycon.Close();Line 180: again i really appreciate.Thanks
I am trying to write a query that will use tables in more than one SQL database (on the same server). How do refer to the table? I have tried dbasename.tablename.fieldname but I don't think that's working.
On my aspx Web page, I want to delete a member from database table 'tblMember', but if this MemberID is used as FK in another table, I want to display a user friendlier message like "You cannot delete this member, ....." I am using Try, Catch blocks in my Web Page.
Currently it display this message: "DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_..._....' The conflict occurred in database '...', table 'tblMembers', column 'MemberID'. The statement has been terminated. "
So how should I precisely trap this error? Does anybody know what Exception is it? or what error number in SQL server?
Hi I was wodering how to add an OR statment right in the Check Constraint expression. This is what I am starting with in the database ([zip] like '[0-9][0-9][0-9][0-9][0-9]') and what I want well not exact but this would answer my question ([zip] like '[0-9][0-9][0-9][0-9][0-9] || [A-Z][A-Z][A-Z][A-Z][A-Z]') Thanks for any help
Hi, i want to put a check constraint on one of my tables to make sure that a member doesn't get loaded into the same usergroup twice. Could anyone please help me with this.
Here is some sample code:
Code Block
DECLARE @MyTable TABLE (ID INT IDENTITY(1,1), MemberID INT, ClientID INT, UsergroupID INT) INSERT INTO @MyTable VALUES (123456, 211, 3054) -- Member gets loaded twice INSERT INTO @MyTable VALUES (123456, 211, 3054) INSERT INTO @MyTable VALUES (123456, 211, 3055) -- Same Member as the top but into ---- a different usergroup which is correct INSERT INTO @MyTable VALUES (215489, 376, 2017) INSERT INTO @MyTable VALUES (987512, 345, 2237) INSERT INTO @MyTable VALUES (235479, 333, 1158) INSERT INTO @MyTable VALUES (354985, 333, 1158)
SELECT * FROM @MyTable
The problem i have is that if a member is loaded into the same usergroup twice then the sites crash on the web applications and the member can't get into his profile page.Unfortunately i don't know which one of the 1000 stored procedures are used to access this table so that i could just change the query to SELECT TOP 1.
However i think that it will be better practive to just put a check contraint on the table and avoide the whole problem.
However 1 member can belong to multiple usergroups.
In our database we have an indexed field that is using unique values, so in the index we turned on the unique property. Now with some changes we made in the application this value can be Null (could not be Null in the past). When we have more as 1 value having value Null we get an exception of unique key violation.
Therefor we want to make an check constraint the checks if the value allready exists when the value is not Null.
Hi, I am trying to enforce a rule that only one record in a table can have status 'Active'. I'd like to do this with CHECK constraint rather than trigger. I know SQL server doesn't support subqueries such as : alter table add constraint chk_OnlyOneActive CHECK (1=(select count(*) from mytable where status='Active')) Is there any workaround for this? P.S. I tried using UDF like this: alter table add constraint chk_OnlyOneActive CHECK (dbo.NumActiveRecords()=1) and it works for insert - I am not able to insert a second "Active" record, but for some reason doesn't work for update - I am able to update existing record to status "Active" and end up with two or more Active records in the table. Thank you.
