Hi Everyone,  I ran the below INSERT COMMAND and it resulted into the following error message. I will be glad if someone can help me solve this in order to move ahead.  You can also see the table structure below.

NB: Those are the only two tables that reference the Artist table.

SET IDENTITY_INSERT dbo.ARTIST ON

INSERT INTO ARTIST
(ArtistID,LastName,FirstName,Nationality,DateOfBirth,DateDeceased)
VALUES(1, 'Miro', 'Joan', 'Spanish', 1893, 1983);

SET IDENTITY_INSERT dbo.ARTIST OFF

Msg 547, Level 16, State 0, Line 4
The INSERT statement conflicted with the CHECK constraint "ValidBirthYear". The conflict occurred in database "VRG", table "dbo.ARTIST", column 'DateOfBirth'.
The statement has been terminated.

TABLE STRUCTURE 

CREATE TABLE ARTIST(
ArtistID  int NOT NULL IDENTITY(1,1),
LastName Char(25) NOT NULL,
FirstName Char(25)  NOT NULL,
Nationality Char(30)  NULL,
DateOfBirth Numeric(4)  NULL,
DateDeceased Numeric(4) NULL,
CONSTRAINT ArtistPK PRIMARY KEY(ArtistID),
CONSTRAINT ArtiskAK1 UNIQUE(LastName,FirstName),
CONSTRAINT NationalityValues CHECK
(Nationality IN ('Canadian', 'English','French',
'German', 'Mexican', 'Russian', 'Spanish',
'United States')),
CONSTRAINT BirthValuesCheck CHECK ( DateOfBirth < DateDeceased),
CONSTRAINT ValidBirthYear CHECK
(DateOfBirth       LIKE   '[1-2] [0-9] [0-9] [0-9]'),
CONSTRAINT ValidDeathYear CHECK
(DateDeceased   LIKE    '[1-2] [0-9] [0-9] [0-9]')

);

CREATE TABLE WORK(
WorkID int NOT NULL IDENTITY (500,1),
Title Char(35) NOT NULL,
Copy Char(12) NOT NULL,
Medium Char(35) NULL,
[Description] Varchar(1000) NULL DEFAULT 'Unknown provenance',
ArtistID int NOT NULL,
CONSTRAINT WorkPK  PRIMARY KEY(WorkID),
CONSTRAINT WorkAK1   UNIQUE(Title,Copy),
CONSTRAINT ArtistFK  FOREIGN KEY (ArtistID)
REFERENCES ARTIST(ArtistID)
 ON UPDATE NO ACTION
ON DELETE NO ACTION
 
);

    

CREATE TABLE CUSTOMER_ARTIST_INT(
ArtistID     Int  NOT NULL,
CustomerID Int NOT NULL,
CONSTRAINT CAIntPK PRIMARY KEY(ArtistID,CustomerID),
CONSTRAINT CAInt_ArtistFK FOREIGN KEY(ArtistID)
REFERENCES ARTIST(ArtistID)
 ON UPDATE NO ACTION
 ON DELETE CASCADE,
CONSTRAINT CAInt_CustomerFK FOREIGN KEY(CustomerID)
REFERENCES CUSTOMER(CustomerID)
    ON UPDATE NO ACTION
     ON DELETE CASCADE,

);

• Edited by CWreh 17 hours 42 minutes ago

DateOfBirth Numeric(4)              NULL,
....
CONSTRAINT ValidBirthYear
CHECK
(DateOfBirth       LIKE   '[1-2] [0-9] [0-9] [0-9]'),
....

Hello,

The column "DateOfBirth" is of type numeric and in the constrain you perform a textual pattern match?!?!

Thanks for the feedback . Being a novice, I still don't understand what you mean by "perform a textual pattern...".  he script was obtained from a  book that I am using and I really don't know how to fix this.  I will appreciate your contribution.

Perhaps you should get another book.  The quick answer is that there are spaces in your year expression that shouldn't be there.  Instead of:

CONSTRAINT ValidBirthYear CHECK
(DateOfBirth       LIKE   '[1-2] [0-9] [0-9] [0-9]'),

You should have:

CONSTRAINT ValidBirthYear CHECK
(DateOfBirth       LIKE   '[1-2][0-9][0-9][0-9]'),

But I would argue that this is a bad example for learning purposes.  The DateOfBirth column is numeric so attempting to use a string-based pattern as a constraint is adding unnecessary complexity.  The constraint logically intends to limit the possible values in that column to the domain of [1000 ... 2999].  That is far easier to implement and understand using

CONSTRAINT ValidBirthYear CHECK
(DateOfBirth       between 1000 and 2999),

Note also that the ValidDeathYear constraints suffers from the same problem. And I also suggest that a column named DateOfBirth that is numeric and stores only a 4 digit number is not correctly named.  Another reason to suggest that your source material needs work.

Thanks. I appreciate your help and advice.