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?
A.
A FOREIGN KEY constraint
B.
A trigger
C.
A user-defined data type
D.
A CHECK constraint
It’s not recommendable to impement a user-defined data type (Answer C) with a Rule because that feature will be removed in a future version of Microsoft SQL Server. (https://msdn.microsoft.com/en-us/library/ms188064.aspx)
The other way to implement a user-defined data type would be a CLR-Function, but I think the effort for that would be too big.
(The solution must minimize the amount of development effort required.)
Unless SKU would occur in a large number of tables, but this information does not appear clearly from the question.
So I think ‘D’ is the right Answer:
CREATE TABLE dbo.Product (
ProductID INT IDENTITY(1,1) Primary Key NOT NULL,
ProductName NVARCHAR(30) NOT NULL,
SKU NVARCHAR(13) NOT NULL
CONSTRAINT CHK_SKU
CHECK (SKU like ‘ADV[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]’)
);
GO
0
0
Agreed.
0
0
I agree – the correct answer is CHECK constraint (D). At the same time I have 3 comments:
1) The questions says that DATABASE has multiple tables which contain product
information, but it does not say that each TABLE has a stock-keeping unit (SKU). It says each PRODUCT has a stock-keeping unit, which might mean that there is only one table which contains SKU.
2)”The other way to implement a user-defined data type would be a CLR-Function”. If you implement a CLR data type, then it will be not a user-defined data type (UDDT). It will be user-defined type(UDT), but we don’t have such option in the question.
3)SKU NVARCHAR(13) NOT NULL CONSTRAINT CHK_SKU CHECK (SKU like ‘ADV[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]’ looks good, but I would use char(13) in this case.
0
0
I agree with (C), as there are multiple tables.
0
0