Question

A vehicle’s VIN is very important for any and all paperwork, such as lease agreements, insurance details, servicing schedules, etc. The code needed to check a 17-digit VIN can be very cumbersome. So instead, we’ll use a simplified version of a VIN for this database, which is defined as follows:

• A VIN is a string of exactly 5 characters

• Each character in a VIN is a digit or an uppercase letter

• A VIN can contain any of the digits 0 to 9

• A VIN can contain any uppercase letter except I, O, and Q

• The 3rd character of a VIN is either a digit from 0 to 9 or the letter X.

Use SQL check constraints to make sure this is implemented in your database. A check constraint is a boolean expression associated with a single column using the keyword CHECK. Every time the value in that column is altered (or inserted) the system will check that the boolean expression is still true with the new value. The system will then check the condition when an UPDATE statement is attempted, and prohibit the operation if the changed value violates the condition. Add a CHECK constraint to the table Vehicle to ensure that the VIN always meets the basic requirements above. You may need to look up the documentation for CHECK on sqlite.org to double-check the exact syntax.

Answer 1

CREATE TABLE Vehicle (ID int NOT NULL,LastName varchar(255) NOT NULL,FirstName varchar(255),VIN varchar(255),CONSTRAINT CHK_vin CHECK (VIN LIKE '[0123456789WERTYUPASDFGHJKLZXCVBNM][0123456789WERTYUPASDFGHJKLZXCVBNM][0123456789X][0123456789WERTYUPASDFGHJKLZXCVBNM][0123456789WERTYUPASDFGHJKLZXCVBNM]' ));

This sql statement create the CHECK constraint for VIN while creating table

UPDATE Vehicle SET VIN = 'AB123' WHERE ID=1001 ;

This statement updates VIN

ALTER TABLE Vehicle ADD CONSTRAINT CHK_vin CHECK ( VIN LIKE '[0123456789WERTYUPASDFGHJKLZXCVBNM][0123456789WERTYUPASDFGHJKLZXCVBNM][0123456789X][0123456789WERTYUPASDFGHJKLZXCVBNM][0123456789WERTYUPASDFGHJKLZXCVBNM]');

If the table is altered, It is checked by CHECK .

Here in,

CHECK ( VIN LIKE '[0123456789WERTYUPASDFGHJKLZXCVBNM]

[0123456789WERTYUPASDFGHJKLZXCVBNM]

[0123456789X]

[0123456789WERTYUPASDFGHJKLZXCVBNM]

[0123456789WERTYUPASDFGHJKLZXCVBNM]'

As there [] specifies what values it contains.

[0123456789WERTYUPASDFGHJKLZXCVBNM] specifies a digit or an uppercase letter except I,O,Q because I,O,Q is not specified inside []

For 3rd element we used [0123456789X] to specify digit or X

We used [] five times to ensure VIN has exactly 5 characters.