MS SQL Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed Forums > Databases > MS SQL Development
Reload this Page

update table, foreign keys etc

Discuss update table, foreign keys etc in the MS SQL Development forum on Dev Shed.
update table, foreign keys etc MS SQL Development forum discussing administration, MS SQL queries, and other MS SQL-related topics. SQL Server is Microsoft's enterprise database engine.

Closed Thread
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old August 16th, 2004, 08:01 AM
sql asp sql asp is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jul 2004
Location: UK
Posts: 259 sql asp User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 4 h 19 m 51 sec
Reputation Power: 5
update table, foreign keys etc
I have two tables as below. I want to update a quote and change the item for which it is for. So I want to do an update statement to change the cat_ref that the quote is for. However, when I do this I get a foreign key conflict with cat_ref in the item table. How do I get around this? Thanks

Code:
CREATE TABLE Item (	
	cat_ref VARCHAR(5)PRIMARY KEY,
	descrip VARCHAR(50), 
	date_added SMALLDATETIME,
	cat_type VARCHAR(20))


CREATE TABLE has_quote (	
	quote_id INT IDENTITY (1,1) PRIMARY KEY,
	date_last_pricecheck SMALLDATETIME,
	cat_ref VARCHAR(5)FOREIGN KEY 
		REFERENCES Item(cat_ref)
                         ON DELETE CASCADE,
	first_name VARCHAR(10),
	surname VARCHAR(10),
	FOREIGN KEY (first_name, surname) 
		REFERENCES Customer(first_name, surname)
ON DELETE CASCADE
ON UPDATE CASCADE)
Reply With Quote
  #2  
Old August 18th, 2004, 02:28 PM
victorpendleton victorpendleton is offline
Contributing User
Dev Shed Regular (2000 - 2499 posts)
  
Join Date: Jan 2003
Location: No es importante
Posts: 2,065 victorpendleton User rank is Private First Class (20 - 50 Reputation Level)victorpendleton User rank is Private First Class (20 - 50 Reputation Level) 
Time spent in forums: 6 h 44 m 30 sec
Reputation Power: 8
Does the cat_ref exists in the item table?
__________________
El éxito consiste en una serie de pequeñas victorias día a día

MySQL, MS SQL, MS ACCESS, Oracle Database Manager - http://victorpendleton.net/products/psdviewer.html
Reply With Quote
  #3  
Old August 19th, 2004, 09:49 AM
sql asp sql asp is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jul 2004
Location: UK
Posts: 259 sql asp User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 4 h 19 m 51 sec
Reputation Power: 5
Hi, yes it does.... I've changed my tables slightly and am still getting foreign key conflict errors with cat_ref when inserting into and updating has_quote.

Code:
CREATE TABLE Item (	
	cat_ref VARCHAR(5)PRIMARY KEY,
	descrip VARCHAR(50), 
	date_added SMALLDATETIME,
	cat_type VARCHAR(20),



CREATE TABLE has_quote (	
	quote_id INT IDENTITY (1,1) PRIMARY KEY,
	date_of_quote SMALLDATETIME,
	cat_ref VARCHAR(5)FOREIGN KEY 
		REFERENCES Item(cat_ref),
	first_name VARCHAR(10),
	surname VARCHAR(10),
	customer_phone VARCHAR(20),
	FOREIGN KEY (first_name, surname, customer_phone) 
		REFERENCES Customer(first_name, surname, customer_phone)
ON DELETE CASCADE
ON UPDATE CASCADE)
Reply With Quote
  #4  
Old August 20th, 2004, 12:26 PM
Cletus Cletus is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Aug 2004
Posts: 1 Cletus User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Quote:
Originally Posted by sql asp
Hi, yes it does.... I've changed my tables slightly and am still getting foreign key conflict errors with cat_ref when inserting into and updating has_quote.

Code:
CREATE TABLE Item (	
	cat_ref VARCHAR(5)PRIMARY KEY,
	descrip VARCHAR(50), 
	date_added SMALLDATETIME,
	cat_type VARCHAR(20),



CREATE TABLE has_quote (	
	quote_id INT IDENTITY (1,1) PRIMARY KEY,
	date_of_quote SMALLDATETIME,
	cat_ref VARCHAR(5)FOREIGN KEY 
		REFERENCES Item(cat_ref),
	first_name VARCHAR(10),
	surname VARCHAR(10),
	customer_phone VARCHAR(20),
	FOREIGN KEY (first_name, surname, customer_phone) 
		REFERENCES Customer(first_name, surname, customer_phone)
ON DELETE CASCADE
ON UPDATE CASCADE)



Google Brought me to this thread. I'm having the same problem. I'm trying to alter an existing table to add foreign keys.

When I try to do the following:

ALTER TABLE Cancer_Normalized_Table ADD CONSTRAINT valid_MSA
FOREIGN KEY(MSAKey) REFERENCES MSA_KEYS(FK)

I'm getting the error:

ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'valid_MSA'. The conflict occurred in database 'Cancer', table 'MSA_KEYS', column 'FK'.
Reply With Quote
  #5  
Old August 20th, 2004, 12:30 PM
victorpendleton victorpendleton is offline
Contributing User
Dev Shed Regular (2000 - 2499 posts)
  
Join Date: Jan 2003
Location: No es importante
Posts: 2,065 victorpendleton User rank is Private First Class (20 - 50 Reputation Level)victorpendleton User rank is Private First Class (20 - 50 Reputation Level) 
Time spent in forums: 6 h 44 m 30 sec
Reputation Power: 8
sql_asp can you post the insert/update syntax you are using?
Reply With Quote
  #6  
Old August 23rd, 2004, 03:15 AM
sql asp sql asp is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jul 2004
Location: UK
Posts: 259 sql asp User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 4 h 19 m 51 sec
Reputation Power: 5
Sure...

Code:
SQLStmt = "INSERT into has_quote (date_of_quote,cat_ref, first_name,surname,customer_phone) "
SQLStmt = SQLStmt & "VALUES ('" & date_of_quote & "', '" & cat_ref & "', '" & first_name & "', '" & surname & "', '" & customer_phone & "')"


SQLStmt = "UPDATE has_quote SET " &_
"date_of_quote='" & date_of_quote & "', " &_ 
"cat_ref='" & cat_ref & "' " &_ 
"WHERE quote_id='" & quote_id & "' "
Reply With Quote
Closed Thread

Viewing: Dev Shed Forums > Databases > MS SQL Development > update table, foreign keys etc

« Previous Thread | Next Thread »

Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Linear Mode Linear Mode
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 5 hosted by Hostway. 
Stay green...Green IT