You plan to create a database that has multiple tables. The tables will contain product information. Each product has a stock-keeping unit (SKU).
You need to recommend a solution to ensure that each SKU starts with the letters "ADV" and is followed by 10 digits.
The solution must minimize the amount of development effort required.
What should you include in the recommendation?
To ensure that each SKU starts with the letters 'ADV' and is followed by 10 digits, the best solution is to use a CHECK constraint. A CHECK constraint can enforce a specific format on the SKU column during insert or update operations by ensuring that the value matches the required pattern (in this case, 'ADV' followed by 10 digits). This approach minimizes development effort compared to creating user-defined data types or triggers, which involves more complex implementation and maintenance.
can't this be achieved with something like; CHECK (SKU LIKE 'ADV[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9])
Seems Ok. I'm not sure whether to use CHECK with UDF or this, but probably creating of User-Defined Data Types is less complicated than CHECK with UDF.
I tried with CHECK CONSTRAINT and it's ok CREATE TABLE Product( SKU VARCHAR(50) NOT NULL, CONSTRAINT CHK_SKU CHECK (SKU LIKE 'AVD[0-9][0-9]' OR SKU NOT LIKE 'AVD%') ) INSERT INTO Product VALUES('AVD29') --Success INSERT INTO Product VALUES('AVD356') --Fail INSERT INTO Product VALUES('AVD') --Fail INSERT INTO Product VALUES('SFC') --Success GO SELECT * FROM Product GO DROP TABLE Product GO