CODEWITHSUNDEEP
sqlsql-server
kapz
kapz

Reputation: 457

Add nullable datetime column to Primary Key

I have a table with following columns:

[ClauseID] [int] NOT NULL,
[PolicyCategoryID] [int] NOT NULL,
[ExpiryDate] [smalldatetime] NULL,

By now ClauseID and PolicyCategoryID together creates the primary key. But I want ExpiryDate also be a part of primary key. To make the column not null I tried the following but it gives an error:

ALTER TABLE tblClauses_PolicyCategory
ALTER COLUMN [ExpiryDate] SMALLDATETIME NOT NULL DEFAULT '2079-06-06'

Incorrect syntax near the keyword 'DEFAULT'.

Any idea why? Is it not possible to set a default date like this?

EDIT: By bad! Default key was already set. That must be the reason it gave an error.

Upvotes: 2

Views: 7493

Answers (2)

veljasije
veljasije

Reputation: 7102

I think this will help you:

CREATE TABLE tblClauses_PolicyCategory(
[ClauseID] [int] NOT NULL,
[PolicyCategoryID] [int] NOT NULL,
[ExpiryDate] [smalldatetime] NULL)


ALTER TABLE tblClauses_PolicyCategory
ALTER COLUMN [ExpiryDate] SMALLDATETIME NOT NULL 

ALTER TABLE tblClauses_PolicyCategory
ADD CONSTRAINT cons_default DEFAULT '2079-06-06' FOR ExpiryDate

But, before changing ExpireDate to NOT NULL, you must populate values for existing rows in this column, and then change column to NOT NULL.

Upvotes: 0

Joe Taras
Joe Taras

Reputation: 15399

try this:

ALTER TABLE tblClauses_PolicyCategory
ALTER COLUMN [ExpiryDate] SMALLDATETIME NOT NULL

ALTER TABLE tblClauses_PolicyCategory ADD CONSTRAINT
cons_expiryDate DEFAULT '2079-06-06' FOR ExpiryDate

Before execute these lines, please check if exists some rows with ExpiryDate null, if yes, please, update all nullable rows to default value

Upvotes: 2

Related Questions