How To Apply the Condition 'book id which consists of six alphanumeric characters and starts wi-CodePudding

I have written a 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.

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

CodePudding user response：

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

CodePudding user response：

Better will be :

CREATE TABLE book(
bookid VARCHAR(6) CHECK (bookid LIKE 'B'   REPLICATE([A-Z0-9], 5)) ,
...
)

If you want only letters drop the "0-9" part of the like
If you do not want diacritic characters (accents, ligature and so on...) just add a COLLATE operator after the "bookid" in the CHECK constraint definition with a AI collation like Latin1_General_CI_AS
If you want only capital letters, use a CS collations like Latin1_General_CS_AI
If you want both (no diacrictics and capitals) use a CS_AS collation
If you want only lower characters replace [A-Z0-9] by [a-z0-9] with CS collation