[SQL] Default Values - Tilted Forum Project Discussion Community

Zyr · 04-16-2006, 04:02 AM

Can anyone see why this isn't working?

I created the table as such:

Code:

create table members
(
member_id int not null, fname varchar(32) not null, lname varchar(32) not null, 
 address varchar(255) not null, phone varchar(10) not null, bond int not null DEFAULT 50,
primary key (member_id)
)

The important part is "bond int not null DEFAULT 50"

Now, that part works, at least it creates the table, however, if my understanding is right (and apparently it's not) I should be able to do

Code:

insert into members values (1, 'firstname', 'lastname', 'address', '12345')

leaving out the last part, as it should default to 50.

Unfortunately, I keep getting "Insert Error: Column name or number of supplied values does not match table definition."

Can anyone help?

Silvy · 04-16-2006, 05:54 AM

The problem with this is that you supply 5 values for 6 columns.
Apparently the SQL server cannot determine which value you're missing.**

Try using this statement:

Code:

insert into members set member_id=1, fname='firstname', lname='lastname', address='address', phone='12345'

This way you explicitly specify which values you're setting, and the server should take the default for all other columns.

**Think of it this way: if there are 2 columns with a default setting, how would the server know which value you didn't specify?
Only for the case where colums-with-default-values equals the number-of-missing-values is there a 'correct' way to parse the statement.

HTH

ratbastid · 04-16-2006, 06:20 AM

When you don't supply a field list in your INSERT statment, you have to have the right number of fields in your VALUES list. You might think that SQL would just line them up one-for-one and NULL (or default) any fields at the end, but not so.

Try this:

Code:

INSERT INTO members (id, fname, lname, address, phone) VALUES (1, 'firstname','lastname','address','12345');

That should do the trick.

Zyr · 04-16-2006, 06:55 PM

Thanks guys, I'll give it a go (as I'm typing this)

...

Cool, it worked. It makes sense now, too.

Thanks again.

04-16-2006, 04:02 AM	#1 (permalink)
Zyr Crazy Location: Hamilton, NZ	[SQL] Default Values Can anyone see why this isn't working? I created the table as such: Code: create table members ( member_id int not null, fname varchar(32) not null, lname varchar(32) not null, address varchar(255) not null, phone varchar(10) not null, bond int not null DEFAULT 50, primary key (member_id) ) The important part is "bond int not null DEFAULT 50" Now, that part works, at least it creates the table, however, if my understanding is right (and apparently it's not) I should be able to do Code: insert into members values (1, 'firstname', 'lastname', 'address', '12345') leaving out the last part, as it should default to 50. Unfortunately, I keep getting "Insert Error: Column name or number of supplied values does not match table definition." Can anyone help? __________________ "Oh, irony! Oh, no, no, we don't get that here. See, uh, people ski topless here while smoking dope, so irony's not really a high priority. We haven't had any irony here since about, uh, '83 when I was the only practitioner of it, and I stopped because I was tired of being stared at." Omnia mutantu, nos et mutamur in illis. All things change, and we change with them. - Neil Gaiman, Marvel 1602

04-16-2006, 05:54 AM	#2 (permalink)
Silvy paranoid Location: The Netherlands	The problem with this is that you supply 5 values for 6 columns. Apparently the SQL server cannot determine which value you're missing. Try using this statement: Code: insert into members set member_id=1, fname='firstname', lname='lastname', address='address', phone='12345' This way you explicitly specify which values you're setting, and the server should take the default for all other columns. Think of it this way: if there are 2 columns with a default setting, how would the server know which value you didn't specify? Only for the case where colums-with-default-values equals the number-of-missing-values is there a 'correct' way to parse the statement. HTH __________________ "Do not kill. Do not rape. Do not steal. These are principles which every man of every faith can embrace. " - Murphy MacManus (Boondock Saints)

04-16-2006, 06:20 AM	#3 (permalink)
ratbastid Darth Papa Location: Yonder	When you don't supply a field list in your INSERT statment, you have to have the right number of fields in your VALUES list. You might think that SQL would just line them up one-for-one and NULL (or default) any fields at the end, but not so. Try this: Code: INSERT INTO members (id, fname, lname, address, phone) VALUES (1, 'firstname','lastname','address','12345'); That should do the trick.

04-16-2006, 06:55 PM	#4 (permalink)
Zyr Crazy Location: Hamilton, NZ	Thanks guys, I'll give it a go (as I'm typing this) ... Cool, it worked. It makes sense now, too. Thanks again. __________________ "Oh, irony! Oh, no, no, we don't get that here. See, uh, people ski topless here while smoking dope, so irony's not really a high priority. We haven't had any irony here since about, uh, '83 when I was the only practitioner of it, and I stopped because I was tired of being stared at." Omnia mutantu, nos et mutamur in illis. All things change, and we change with them. - Neil Gaiman, Marvel 1602