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 Friday, May 29, 2015 1:23 PM