How to apply the condition 'book id which consists of six alphanumeric characters and starts with B' during creating table in SQL?

Question

My query in SQL to create 'book' table(database):

CREATE TABLE book(
bookid VARCHAR(6) where bookid LIKE 'B_____',
bookTitle VARCHAR(50),
author VARCHAR(20),
genre VARCHAR(10) CHECK(genre IN('Mystery','Thriller')),
yearPublication INTEGER
);

When I ran this code it shows error:

missing right parenthesis.

On second line (bookid VARCHAR(6) where LIKE 'B_____'), when I remove the condition (where LIKE 'B_____') the table is successfully created but according to problem statement the condition must be there to achieve the demands. Could please anyone provide the correction in this code after executing?

Answer 1

You can use a check constraint like the constraint concerning the genre which is already part of your command. So your create table command will be:

CREATE TABLE book(bookid VARCHAR(6) 
CHECK (bookid LIKE 'B_____'), 
bookTitle VARCHAR(50), 
author VARCHAR(20), 
genre VARCHAR(10) 
CHECK(genre IN('Mystery','Thriller')), 
yearPublication INTEGER);

If you want to set names for the constraints (also for the genre constraint), this will work:

CREATE TABLE book(bookid VARCHAR(6)
CONSTRAINT chkBookId CHECK (bookid LIKE 'B_____'), 
bookTitle VARCHAR(50), 
author VARCHAR(20), 
genre VARCHAR(10) 
CONSTRAINT chkGenre CHECK (genre IN('Mystery','Thriller')), 
yearPublication INTEGER);

If the table already exists (maybe because you executed the create table command without the constraint) and you need to add the constraint, you can do following:

ALTER TABLE book 
ADD CONSTRAINT chkBookId
CHECK (bookid LIKE 'B_____');

Please note: If you really need a restriction for alphanumeric characters instead of allowing any characters, you have to change the commands listed above. You can use a regex like this:

CREATE TABLE book(bookid VARCHAR(6) 
CONSTRAINT chkBookId CHECK (bookid LIKE 'B_____' 
AND PATINDEX('%[^a-zA-Z0-9 ]%',bookid) = 0), 
bookTitle VARCHAR(50), 
author VARCHAR(20), 
genre VARCHAR(10) 
CONSTRAINT chkGenre CHECK(genre IN('Mystery','Thriller')), 
yearPublication INTEGER);

Please see this is working correctly here: db<>fiddle