[SQL] Default Values

[Zyr]

Can anyone see why this isn't working?

I created the table as such:

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

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]

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:

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]

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:

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

That should do the trick.

[Zyr]

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

...

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

Thanks again.