You are a database developer responsible for maintaining an application. The application has a table named Programs that has the following definition:
CREATE TABLE
[dbo].[Customers]([ID] int NOT NULL IDENTITY(1,1),
[Name] varchar(100) NOT NULL,
[Address] varchar(255) NOT NULL,
[City] varchar(100) NOT NULL,
[State] char(2) NOT NULL,
[Postal] varchar(5) NOT NULL,
[Priority] char(1) NOT NULL,
[Active] bit NOT NULL)
GO
ALTER TABLE [dbo].[Customers] ADD CONSTRAINT PK_Customers PRIMARY KEY NONCLUSTERED (Name)
GO
You need to modify the Customers table to meet the following requirements:
•ID must be the Primary Key.
•The clustered index must be on the ID column.
•The Active column must have a default value of 1, and must allow values of only 0 or 1.
•The Priority column must have values of "a", "b", or "c".
•The Postal column must contain a correctly formatted five-digit numeric Postal Code.
Which Transact-SQL statement or statements should you use?
A. ALTER TABLE [dbo].[Customers] ADD CONSTRAINT PK_Customers PRIMARY KEY CLUSTERED (ID)
ALTER TABLE [dbo].[Customers] ADD CONSTRAINT CK_Priority CHECK ([Priority] IN (‘a’, ‘b’,’c’))
ALTER TABLE [dbo].[Customers] ADD CONSTRAINT CK_Postal CHECK ([Postal] LIKE ‘[0-9][0-9][0-9][0-9][0-9]’)
ALTER TABLE [dbo].[Customers] ADD CONSTRAINT DF_Active DEFAULT (1) FOR [Active]
ALTER TABLE [dbo].[Customers] ADD CONSTRAINT CK_Active CHECK ([Active] IN (0,1))
B. ALTER TABLE [dbo].[Customers] DROP CONSTRAINT PK_Customers
ALTER TABLE [dbo].[Customers] ADD CONSTRAINT PK_Customers PRIMARY KEY CLUSTERED (ID)
ALTER TABLE [dbo].[Customers] ADD CONSTRAINT CK_Priority CHECK ([Priority] IN (‘a’, ‘b’, ‘c’))
ALTER TABLE [dbo].[Customers] ADD CONSTRAINT CK_Postal CHECK ([Postal] LIKE ‘[0-9][0-9][0-9][0-9][0-9]’)
ALTER TABLE [dbo].[Customers] ADD CONSTRAINT DF_Active DEFAULT (1) FOR [Active]
ALTER TABLE [dbo].[Customers] ADD CONSTRAINT CK_Active CHECK ([Active] IN (0,1))
C. ALTER TABLE [dbo].[Customers] ADD CONSTRAINT PK_Customers PRIMARY KEY CLUSTERED (ID)
ALTER TABLE [dbo].[Customers] ADD CONSTRAINT CK_Priority VALIDATE ([Priority] IN (‘a’, ‘b’,’c’))
ALTER TABLE [dbo].[Customers] ADD CONSTRAINT CK_Postal VALIDATE ([Postal] LIKE ‘[0-9][0-9][0-9][0-9][0-9]’)
ALTER TABLE [dbo].[Customers] ADD CONSTRAINT DF_Active DEFAULT (1) FOR [Active]
ALTER TABLE [dbo].[Customers] ADD CONSTRAINT CK_Active VALIDATE ([Active] IN (0,1))
D. ALTER TABLE [dbo].[Customers] DROP CONSTRAINT PK_Customers
ALTER TABLE [dbo].[Customers] ADD CONSTRAINT PK_Customers PRIMARY KEY NONCLUSTERED (ID)
ALTER TABLE [dbo].[Customers] ADD CONSTRAINT CK_Priority CHECK ([Priority] IN (‘a’, ‘b’, ‘c’))
ALTER TABLE [dbo].[Customers] ADD CONSTRAINT CK_Postal CHECK ([Postal] LIKE ‘[0-9][0-9][0-9][0-9][0-9]’)
ALTER TABLE [dbo].[Customers] ADD CONSTRAINT DF_Active DEFAULT (1) FOR [Active]
ALTER TABLE [dbo].[Customers] ADD CONSTRAINT CK_Active CHECK ([Active] IN (0,1))
E. ALTER TABLE [dbo].[Customers] DROP CONSTRAINT PK_Customers
ALTER TABLE [dbo].[Customers] ADD CONSTRAINT PK_Customers PRIMARY KEY (ID)
ALTER TABLE [dbo].[Customers] ADD CONSTRAINT CK_DataChecks CHECK ([Priority] LIKE ‘[abc]’ AND ISNUMERIC([Postal]) = 1 AND LEN
([Postal]) = 5 AND [Active] IN (0,1))
F. ALTER CONSTRAINT PK_Customers SET CLUSTERED (ID)
ALTER TABLE [dbo].[Customers] ADD CONSTRAINT PK_Customers PRIMARY KEY CLUSTERED (ID)
ALTER TABLE [dbo].[Customers] ADD CONSTRAINT CK_Priority CHECK ([Priority] IN (‘a’, ‘b’, ‘c’))
ALTER TABLE [dbo].[Customers] ADD CONSTRAINT CK_Postal CHECK ([Postal] LIKE ‘[0-9][0-9][0-9][0-9][0-9]’)
ALTER TABLE [dbo].[Customers] ADD CONSTRAINT DF_Active DEFAULT (1) FOR [Active]
ALTER TABLE [dbo].[Customers] ADD CONSTRAINT CK_Active CHECK ([Active] IN (0,1))
G. ALTER TABLE [dbo].[Customers] DROP CONSTRAINT PK_Customers
ALTER TABLE [dbo].[Customers] ADD CONSTRAINT PK_Customers PRIMARY KEY (ID)
ALTER TABLE [dbo].[Customers] ADD CONSTRAINT CK_DataChecks CHECK ([Priority] = ‘[abc]’ AND LEN([Postal]) = 5 AND [Active] IN (0,1))
H. ALTER TABLE [dbo].[Customers] DROP CONSTRAINT PK_Customers
ALTER TABLE [dbo].[Customers] ADD CONSTRAINT PK_Customers PRIMARY KEY (ID)
ALTER TABLE [dbo].[Customers] ADD CONSTRAINT CK_DataChecks CHECK ([Priority] IN ‘[abc]’ AND ISNUMERIC([Postal]) = 1 AND [Active] IN (0,1))
