CODEWITHSUNDEEP
sql-servert-sql
Frepor
Frepor

Reputation: 15

Constraint does not exist in SQL Server

I'm doing a task, in which I should create a table, here's the code:

CREATE TABLE HumanResources.JobCandidateHistory
(
    JobCandidateID int NOT NULL UNIQUE,
    Resume xml NULL,
    Rating int NOT NULL CHECK(Rating BETWEEN 1 AND 10) DEFAULT 5,
    RejectedDate datetime NOT NULL,
    ContactID int FOREIGN KEY REFERENCES Person.Contact(ContactID)
);

So, Rating is checked. Next step in this task is to deactivate this check by:

ALTER TABLE HumanResources.JobCandidateHistory
NOCHECK CONSTRAINT CK_JobCandidateHistory_Rating;
GO

ALTER TABLE HumanResources.JobCandidateHistory
NOCHECK CONSTRAINT DF_JobCandidateHistory_Rating;
GO

But doing this, I get an error:

Msg 4917, Level 16, State 0, Line 4
Constraint 'CK_JobCandidateHistory_Rating' does not exist.

Msg 4916, Level 16, State 0, Line 4
Could not enable or disable the constraint. See previous errors.

Msg 4917, Level 16, State 0, Line 7
Constraint 'DF_JobCandidateHistory_Rating' does not exist.

Msg 4916, Level 16, State 0, Line 7
Could not enable or disable the constraint. See previous errors.

And I can't understand the reason, why it appears. As I understand, this constraint exists, as the column values are checked.

Upvotes: 1

Views: 1001

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269813

Give the constraints names:

CREATE TABLE HumanResources.JobCandidateHistory(
    JobCandidateID int NOT NULL UNIQUE,
    Resume xml NULL,
    Rating int NOT NULL DEFAULT 5,
    constraint CK_JobCandidateHistory_Rating  check (Rating BETWEEN 1 AND 10),
    RejectedDate datetime NOT NULL,
    ContactID int,
    constraint DF_JobCandidateHistory_Rating FOREIGN KEY REFERENCES Person.Contact(ContactID)
);

The internal names are quite different from user-defined names.

Upvotes: 4

Related Questions