Tilted Forum Project Discussion Community

Tilted Forum Project Discussion Community (https://thetfp.com/tfp/)

- Tilted Technology (https://thetfp.com/tfp/tilted-technology/)

- - [SQL] Default Values (https://thetfp.com/tfp/tilted-technology/103477-sql-default-values.html)

Zyr	04-16-2006 04:02 AM

[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?

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.

All times are GMT -8. The time now is 09:50 AM.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76