Insert with 2 DEFAULT constraints in a row

Question

Let's say I have a create table script like that:

CREATE TABLE Person (
    id INT PRIMARY KEY,
    age INT DEFAULT 18,
    iq INT DEFAULT 100
);

So I have two DEFAULT constraints in a row. Now I make an insertion like that:

INSERT INTO Person VALUES(1,85);

How do we know if we skip the attribute age or the attribute iq?

Thank you for your help.

You should edit your question and specify which type of SQL in the tags. — devlin carnate, Feb 28 '18 at 21:51
Voting to close as Too Broad. There are way too many types of RDMS that an answer would need to cover in order to be considered complete. — devlin carnate, Feb 28 '18 at 21:59
But it seems that rollstuhlfahrer's answer managed perfectly to do that. We don't have to get into the most obscure RDMS, only the main ones. — JacopoStanchi, Feb 28 '18 at 22:07
That's a default _value_, not a "constraint" - and the INSERT statement is actually invalid standard SQL. — , Feb 28 '18 at 22:11
W3school called it a constraint [here](https://www.w3schools.com/sql/sql_default.asp). What do you mean invalid standard SQL? — JacopoStanchi, Feb 28 '18 at 22:15

score 5 · Accepted Answer · edited Jun 20 '20 at 09:12

The answer to your question: it depends. ;-)

MySQL

Running your query on MySQL will get you the following error:

Error: ER_WRONG_VALUE_COUNT_ON_ROW: Column count doesn't match value count at row 1

For MySQL you need to tell the DBMS which columns you want to have (and which columns the DB should fill with default data). Like this:

INSERT INTO Person(id, age) VALUES(1,85);

Or you need to tell the DB, where to use default values. You can pass the keyword DEFAULT for these columns:

INSERT INTO Person VALUES(1,85, DEFAULT);

SQLite acts like MySQL, the error message here is:

SQLITE_ERROR: table Person has 3 columns but 2 values were supplied

Which means, you have to specify the columns.

Running the query on a PostgreSQL table will yield

id  age iq
1   85  100

Here PostgreSQL will use the columns from the table definition in the defined order and fill all remaining columns with the default value.

Thank you for your quick answer. More specifically for MySQL, isn't there a quicker method than rewriting all the columns, like for instance a DEFAULT clause that we would put in the INSERT? — JacopoStanchi, Feb 28 '18 at 22:04