inserting into table

Dev Shed Forums Sponsor:

SlickEdit: Code in over 40 languages across 7 platforms. SlickEdit’s unmatched power, speed, and flexibility allows even the most accomplished developers to write better code faster. Download a free trial today!

August 24th, 2004, 03:27 AM

sql asp

Contributing User

Join Date: Jul 2004

Location: UK

Posts: 259

Time spent in forums: 4 h 19 m 51 sec
Reputation Power: 4

inserting into table

I have the 3 tables below. I want to be able to add more than one item to a quote. I thought I had designed the tables so that I could have more than one item in a quote....? I've tried to do this:

insert into has_quote(date_of_quote,service_desk_contact,category,cat_ref)
values('09/12/2002', 'me',2,'C92') where quote_id = '1'

but it doesn't work. Am I totally missing something here?

Code:

CREATE TABLE Item (	
	cat_ref VARCHAR(5)PRIMARY KEY,
	descrip VARCHAR(50), 
	date_added SMALLDATETIME,
date_last_pricecheck SMALLDATETIME,
	cat_type VARCHAR(20),
	contract VARCHAR(10),
cost_price SMALLMONEY,
installation_charge SMALLMONEY,
commercial_markup SMALLMONEY,
supplier_name VARCHAR(20),
	supplier_phone VARCHAR(20),
notes VARCHAR(500))


CREATE TABLE has_quote (	
	quote_id INT IDENTITY (1,1) PRIMARY KEY,
	date_of_quote SMALLDATETIME,
	service_desk_contact VARCHAR(20),
	category INTEGER,
	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)

CREATE TABLE Customer (		
	first_name VARCHAR(10),
	surname VARCHAR(10),
	customer_phone VARCHAR(20),
	contract VARCHAR(10),
	location VARCHAR(20),
	email VARCHAR(50),
	cust_id INT IDENTITY (1,1),
PRIMARY KEY (first_name, surname, customer_phone))

August 24th, 2004, 04:00 AM

PrivateJson

Registered User

Join Date: Aug 2004

Location: Denmark

Posts: 24

Time spent in forums: 12 m 4 sec
Reputation Power: 0

Maybe I'm missing the point, but why would you want to use a WHERE clause on an INSERT statement? And your "has_quote" table has a foreign key constraint, so even without the WHERE clause, it'll not work until you have values for the "first_name", "surname" and "customer_phone" fields.

August 24th, 2004, 04:19 AM

sql asp

Contributing User

Join Date: Jul 2004

Location: UK

Posts: 259

Time spent in forums: 4 h 19 m 51 sec
Reputation Power: 4

without a where clause, how do I insert another item into the quote?

August 24th, 2004, 04:41 AM

r937

SQL Consultant

Dev Shed God 25th Plane (17000 - 17499 posts)

Join Date: Feb 2003

Location: Toronto Canada

Posts: 17,149

Time spent in forums: 1 Month 3 Weeks 4 Days 14 h 39 m 56 sec
Reputation Power: 883

Quote:

Originally Posted by sql asp

without a where clause, how do I insert another item into the quote?

using the INSERT statement, without a WHERE clause

and yes, you are allowed to omit values for first_name, surname, and customer_phone -- they will be null

__________________
r937.com | rudy.ca

August 24th, 2004, 04:49 AM

sql asp

Contributing User

Join Date: Jul 2004

Location: UK

Posts: 259

Time spent in forums: 4 h 19 m 51 sec
Reputation Power: 4

but surely I need to specify which quote the item gets inserted into?

August 24th, 2004, 05:58 AM

PrivateJson

Registered User

Join Date: Aug 2004

Location: Denmark

Posts: 24

Time spent in forums: 12 m 4 sec
Reputation Power: 0

Quote:

Originally Posted by r937

using the INSERT statement, without a WHERE clause

and yes, you are allowed to omit values for first_name, surname, and customer_phone -- they will be null

Not so sure about that. Those 3 fields are foreign keys, and last time I checked, foreign keys has to match a value in the table they reference (in this case, the Customer table).

sql asp> The quote you insert into, I believe, is one of the foreign keys, so that the quote refers to the quote it "belong" to !?

August 24th, 2004, 06:03 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 17,149

Time spent in forums: 1 Month 3 Weeks 4 Days 14 h 39 m 56 sec
Reputation Power: 883

Quote:

Originally Posted by PrivateJson

foreign keys has to match a value in the table they reference

yes, except if they are null, then they don't have to

August 24th, 2004, 06:07 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 17,149

Time spent in forums: 1 Month 3 Weeks 4 Days 14 h 39 m 56 sec
Reputation Power: 883

Quote:

Originally Posted by sql asp

but surely I need to specify which quote the item gets inserted into?

this is the central problem, isn't it

that sentence doesn't match your table design

this one does: surely i need to specify which item a quote belongs to?

the item does not know about the quotes

the quotes reference their parent items

August 24th, 2004, 06:09 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 17,149

Time spent in forums: 1 Month 3 Weeks 4 Days 14 h 39 m 56 sec
Reputation Power: 883

oh my $deity, i just went back to your first post --

"I want to be able to add more than one item to a quote"

you can't, not with that table design

#10

August 24th, 2004, 06:31 AM

sql asp

Contributing User

Join Date: Jul 2004

Location: UK

Posts: 259

Time spent in forums: 4 h 19 m 51 sec
Reputation Power: 4

Thanks for your replies

Guess I'll have to do a bit of redesigning. I need to have more than one quote associated with each item and more than one item associated with each quote.... Is this achieveable or am I going to end up going round in circles?

#11

August 24th, 2004, 06:37 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 17,149

Time spent in forums: 1 Month 3 Weeks 4 Days 14 h 39 m 56 sec
Reputation Power: 883

you describe a many-to-many relationship

quote -- primary key quote_id
item -- primary key item_id
quoteitem -- primary key (quote_id,item_id)

each of the two columns in the composite pk of quoteitem, the so-called relationship or linking or junction table -- is a foreign key to its respective parent table, quote or item

thus to link a quote to an item, you need to know which quote it is, and which item it is

the fact that the pair of columns is the pk means that you can only link a specific quote to a specific item at most once

the quote table would probaly have a foreign key to the customer, i.e. each quote is for only one specific customer

#12

August 24th, 2004, 06:49 AM

sql asp

Contributing User

Join Date: Jul 2004

Location: UK

Posts: 259

Time spent in forums: 4 h 19 m 51 sec
Reputation Power: 4

thanks I'll give it a go...

#13

August 24th, 2004, 06:58 AM

sql asp

Contributing User

Join Date: Jul 2004

Location: UK

Posts: 259

Time spent in forums: 4 h 19 m 51 sec
Reputation Power: 4

Quote:

each of the two columns in the composite pk of quoteitem, the so-called relationship or linking or junction table -- is a foreign key to its respective parent table, quote or item

So in the has_quote table, quote_id is a foregin key...? same with the item table for item_id?

#14

August 24th, 2004, 08:26 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 17,149

Time spent in forums: 1 Month 3 Weeks 4 Days 14 h 39 m 56 sec
Reputation Power: 883

if you want it to be called has_quote, that's fine

it needs two foreign keys, one to the item table and one to the quote table (which you need to create)

you can not do a many-to-many relationship with fewer than three tables

your customer table doesn't count, because that will be a separate one-to-many relationship from customer to quote