Is a DEFAULT constraint an actual constraint?-CodePudding

In SQL I see several types of constraints, like PRIMARY KEY. NOT NULL, FOREIGN KEY, etc. But I stumbled on DEFAULT constraints now and I'm confused.

What does it prevent to happen? Will any query fail to adhere by it?

If no query will ever fail because of a DEFAULT constraint, is it really a constraint?

Thank you. A confused developer

CodePudding user response：

if a column is Not NULL

CREATE TABLE mytable (val int NOT NULL)

and you try

 INSERT NTO mytable VALUES (NULL)

You will get an error, as te database can't replace the NULL with a DEFAULT value, because it doesn't exist.

so it constraints the INSERT INTO

CodePudding user response：

Check if the following answer helps you understanding about DEFAULT constraint. https://stackoverflow.com/a/1213316/7056491

CodePudding user response：

If a column has a DEFAULT constraint and the INSERT or UPDATE statement doesn’t provide the value for that column, MySQL will use the default value specified in the DEFAULT constraint.

So for table:

CREATE TABLE Defaults (
    Age int,
    Gender varchar(255) DEFAULT 'Female'
);

You can Insert:

INSERT INTO Defaults (`Age`) VALUES ('4');

Age will be 4, and Gender will be 'Female'.

DEFAULT is a constraint in the sense that it RESTRICTS the insertion of NULL values - It prevents NULL values on specific columns.

Queries that will fail are those that will try to set NULL on columns with DEFAULT constraints. Example:

INSERT INTO Defaults (`Age`, `Gender`) VALUES ('4', NULL);

UPDATE Defaults SET Gender=NULL;