CODEWITHSUNDEEP
sqlsql-servercheck-constraints
Denni S
Denni S

Reputation: 134

SQL Server message 547

I want to make Kode_Barang 7 character with first character Must 'B' and the the remaining character must be a number [1-9]

I have create query for Kode_Barang just like this :

Kode_Barang CHAR(7) PRIMARY KEY CHECK(LEN(Kode_Barang)=7 AND LEFT(Kode_Barang,1)='B' AND SUBSTRING(Kode_Barang,2,6)LIKE '[0-9]' )

But after I insert some values like this :

INSERT INTO Barang (Kode_Barang, Nama_Barang, Satuan, Harga, Stock) 
VALUES('B000001', 'Mie Goreng', 'Biji', 2500, 56)

There are an error occurred on Kode_Barang.

Is there anything wrong? Please I'm new to SQL query with check constraint.

NOTE for another reference :

Nama_Barang VARCHAR(25) UNIQUE NOT NULL,
Satuan VARCHAR (10) CHECK(Satuan = 'Buah' OR Satuan = 'Biji' OR Satuan  = 'CM' OR Satuan = 'KG' OR Satuan = 'DUS' OR Satuan = 'Lusin'),
Harga MONEY NOT NULL CHECK(Harga != 0)
Stock INT NOT NULL CHECK(Stock > -1)

Thanks

I have tried it with '%[^0-9]%' but the result still the same

I get reference like this one How can I use LEFT & RIGHT Functions in SQL to get last 3 characters?

That my datatype is CHAR not VARCHAR.

And I change the code like this :

Kode_Barang CHAR(7) PRIMARY KEY 
     CHECK(LEN(Kode_Barang) = 7 
           AND SUBSTRING(CAST(Kode_Barang AS VARCHAR(7)), 1, 1) = 'B' 
           AND SUBSTRING(CAST(Kode_Barang AS VARCHAR(7)), 2, 6) LIKE '%[^0-9]%' ),

but still the same

Error:

Msg 547, Level 16, State 0, Line 44
The INSERT statement conflicted with the CHECK constraint "CK__Barang__Kode_Bar__5441852A". The conflict occurred in database "Studi_Kasus_Kelompok", table "dbo.Barang", column 'Kode_Barang'.

Thanks

Upvotes: 0

Views: 180

Answers (2)

Felix Pamittan
Felix Pamittan

Reputation: 31879

Your CHECK constraint should be:

CHECK(Kode_Barang LIKE 'B[0-9][0-9][0-9][0-9][0-9][0-9]')

This means that Kode_Barang should exactly be 7 characters that starts with a B and the remaining characters are numbers.

Upvotes: 1

Matt Gibson
Matt Gibson

Reputation: 38238

I'd say your SUBSTRING(Kode_Barang, 2, 6) LIKE '[0-9]' is wrong. LIKE '[0-9]' will compare with a single digit. Perhaps try something like:

... SUBSTRING(Kode_Barang, 2, 6) NOT LIKE '%[^0-9]%' ...

...which checks to see if any character in the resulting string is not numeric.

Upvotes: 1

Related Questions