CODEWITHSUNDEEP
sql-serverformatcheck-constraint
Tran Hoai Nam
Tran Hoai Nam

Reputation: 1363

Check constraint for string with a format in SQL Server

I have a table which is defined like:

create table HLV (
id nvarchar(7) check (id LIKE 'HLV[0-9][0-9][0-9][0-9]') Primary key,
birthday date,
full_name nvarchar(20) NOT NULL,
win int check (win > 0),
lose int check (lose > 0),
draw int check (draw > 0)
)

I execute the code and it works, but when I insert the data into it with

insert into HLV values ('HLV0001',GETDATE(), 10,5,6)

I got the error

Column name or number of supplied values does not match table definition.

Is it because the check is wrong?

Upvotes: 1

Views: 3652

Answers (3)

ughai
ughai

Reputation: 9880

Just to expand of Giorgi Nakeuri's Answer,

When you use insert into HLV without any column names, sql server assumes you are going to pass all the columns defined in the table (except for columns like identity). In your Insert query

insert into HLV values (N'HLV0001',GETDATE(), 10,5,6)

SQL Server is expecting values for all the columns id, birthday,full_name,win,lose,draw However you are only passing values for id, birthday,win,lose,draw, hence the number of values provided does not match the table definition.

If you column full_name allowed NULL values or had a DEFAULT constraint, you could do something like this

insert into HLV(id, birthday,win,lose,draw) values (N'HLV0001',GETDATE(), 10,5,6)

Additionally based on your check constraint on ID, you should change it's type from nvarchar(7) to CHAR(7).

Upvotes: 1

Mark Chesney
Mark Chesney

Reputation: 1152

There are 6 columns in the table, but only 5 values in the insert statement values clause. You can fix the problem by specifying all 6 required values, however I would also strongly recommend to specify the column names in the insert statement to make it more robust to change, more self-documenting. You won't have to be sure of the actual column order. You can omit nullable and defaulted columns. New nullable or defaulted columns can be added without breaking the statement.

ALTER TABLE HLV ADD gender char(1) NULL

INSERT HLV (id, birthday, full_name, win, lose, draw)
VALUES ('HLV0001', GETDATE(), 'first last', 10, 5, 6)

INSERT HLV (full_name, id, birthday)
VALUES ('last, first', 'HLV0002', GETDATE())

There are loads of examples on MSDN.

Upvotes: 1

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

No it is because you missed full_name in your insert statement:

insert into HLV values ('HLV0001',GETDATE(), 'michael jordan' ,10,5,6)

Upvotes: 3

Related Questions