SQL Server : default constraint doesn't work

Question

I have a small problem with my code. I added a default constraint but for some reason it doesn't want to work. My code:

CREATE TABLE oc.students 
(
    stud_id INT PRIMARY KEY IDENTITY (1,1),
    first_name VARCHAR(50) NOT NULL,
    mid_name VARCHAR(50),
    last_name VARCHAR(50) NOT NULL,
    DOB DATE,
    email VARCHAR (255) NOT NULL,
    country VARCHAR(50) NOT NULL,
    phone VARCHAR(20),
    reg_date DATE NOT NULL
);

ALTER TABLE oc.students
ADD DEFAULT GETDATE() FOR [reg_date];

INSERT INTO oc.students (first_name, mid_name, last_name, DOB, email, country, phone, reg_date)
VALUES ('John', '', 'Smith', '1986-12-24', '[email protected]', 'Malta', 123456789, '')

SELECT * 
FROM oc.students

Result:

What could be the problem?

Gordon Linoff · Accepted Answer · 2020-06-13 11:19:59Z

'' and NULL are not the same (well, in any database other than Oracle). The first is a string that happens to have no characters. The second is a SQL constant value that has the semantics of "unknown value" and is often used for missing values.

You are inserting '' which is interpreted as 0, which gets converted to the base date. Actually, the technical term for this is epoch, which is the date a uses to start counting date/time values.

If you want the default, you can use:

VALUES (
    'John',
    '',
    'Smith',
    '1986-12-24',
    '[email protected]',
    'Malta',
    123456789,
    DEFAULT
    )

Or, more commonly, the column is just left out:

INSERT INTO oc.students (
                    first_name,
                    mid_name,
                    last_name,
                    DOB,
                    email,
                    country,
                    phone)
VALUES (
    'John',
    '',
    'Smith',
    '1986-12-24',
    '[email protected]',
    'Malta',
    123456789
    )

Collectives™ on Stack Overflow

SQL Server : default constraint doesn't work

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related