SQL workshop: DEFAULT constraint

In SQL, the DEFAULT constraint sets a default value for a column when no value is specified. When you insert a new record into the table without specifying a value for this column, the column will get its value from the default definition.

Explanation:

Assume you have a users table, and you want to have a column isActive that should default to true if not otherwise specified. You can set this up using the DEFAULT constraint during table creation.

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    isActive BOOLEAN DEFAULT TRUE
);

Now, when you insert a new user without specifying the isActive value, it will default to true.

INSERT INTO users (id, name) VALUES (1, 'John Doe');

If you select this record, you'll see that isActive is set to true even though it was not specified in the insert statement.

Workshop Exercise:

Setup (MySQL):
- Create a books table with columns id, title, author, and isAvailable. Make isAvailable default to true.

    CREATE TABLE books (
        id INT PRIMARY KEY,
        title VARCHAR(100),
        author VARCHAR(100),
        isAvailable BOOLEAN DEFAULT TRUE
    );

Exercise:

a. Insert a book without specifying the isAvailable column. For instance:
```
 INSERT INTO books (id, title, author) VALUES (1, '1984', 'George Orwell');
```
b. Query the table to see the default value:
```
 SELECT * FROM books;
```
You should see that isAvailable is set to true for the '1984' book.

c. Insert another book, this time specifying the isAvailable column:
```
 INSERT INTO books (id, title, author, isAvailable) VALUES (2, 'Brave New World', 'Aldous Huxley', false);
```
d. Query the table again:
```
 SELECT * FROM books;
```
You should see two records: one with isAvailable set to true and the other set to false.
Conclusion:

The DEFAULT constraint allows a column to have a default value when no value is specified during insertion. This can be handy in cases where certain columns should have a predictable fallback value.

Remove Default value

To drop a default value from a column in an SQL table, you will use the ALTER TABLE statement. However, the specific syntax varies depending on the database system you're using.

Here's how you can drop a default value for various database systems:

MySQL / MariaDB:

If you have a table named books and you want to remove the default value from the isAvailable column, you'd execute:

ALTER TABLE books ALTER COLUMN isAvailable DROP DEFAULT;

Whenever altering database structures, always make sure you've backed up your data and thoroughly tested the change in a safe environment before applying it to a production database.

References

SQL DEFAULT Constraint