You have a SQL Server 2012 database that contains a table named Users. The Users
table contains usernames and passwords.
You need to ensure that all new records have a password.
Which code segment should you use?
More than one answer choice may achieve the goal. Select the BEST answer.
A.
Option A
B.
Option B
C.
Option C
D.
Option D
D.
D
The correct answer is D. B will fail when password has a value.
D
D
D and only D
You need to ensure that all new records have a password.
So password must be not null and it’s going to be checked.
Might also be A A not-null constraint is functionally equivalent to creating a check constraint CHECK (column_name IS NOT NULL), but an an explicit not-null constraint is more efficient. The drawback is that you cannot give explicit names to not-null constraints created this way.
How can it be A?! If you drop the Password Column of the dbo.Users table, what becomes of the usernames that already have passwords assigned?!
create table checkcons(
sno int identity(1,1) primary key,
username varchar(255),
password nvarchar(255))
alter table checkcons add constraint ck_test
check (password is null)
insert into checkcons values(‘rom’,”)
/*
Output:
Msg 547, Level 16, State 0, Line 3
The INSERT statement conflicted with the CHECK constraint “ck_test”. The conflict occurred in database “master”, table “dbo.checkcons”, column ‘password’.
The statement has been terminated.
*/
Answer is B. Please use the above code to test.
In your example, field [password] must be null.
But you trying to insert empty stirng:
insert into checkcons values(‘rom’,”)
So You get an error.
Be carefull, empty string is not NULL!
There is no right answer here.
The correct code is:
ALTER TABLE dbo.Users WITH NOCHECK
ADD CONSTRAINT CK_Users_Password
CHECK(PASSWORD IS NOT NULL);
which is kind of similar to option D, but missing an important piece of script.
Option A and C are definitely wrong because we loose information when we drop column or table.
Option B is absolute nonsense. The script will complete sucessfully only if all the passwords for the existing users are NULLs. And then the constraint will not allow to insert password for new records, but question says that we need to be ensure that all new records HAVE passwords.