CREATE DATABASE TINYVIDEO

CREATE TABLE MEMBERSHIP
(
  MEM_NUM varchar(3) PRIMARY KEY,
  MEM_FNAME varchar(30),
  MEM_LNAME varchar(30),
  MEM_STREET varchar(30),
  MEM_CITY varchar(20),
  MEM_STATE varchar(2) ,
  CONSTRAINT chk_state CHECK( MEM_STATE in('TN','KY')),
  MEM_ZIP varchar(5) ,
  MEM_BALANCE int,
);

Create TABLE RENTAL
(
    RENT_NUM    varchar(4),
    RENT_DATE    date,
    MEM_NUM    varchar(3),
    PRIMARY KEY (RENT_NUM),
    FOREIGN KEY (MEM_NUM) REFERENCES MEMBERSHIP(MEM_NUM)
)

CREATE TABLE PRICE
(
   PRICE_CODE int,
   PRICE_DESCRIPTION varchar(30),
   PRICE_RENTFEE decimal(2,1),
   PRICE_DAILYLATEFEE decimal(2,1),
   PRIMARY KEY (PRICE_CODE)
);


CREATE TABLE MOVIE
(
   MOVIE_NUM varchar(4),
   MOVIE_TITLE varchar(30),
   MOVIE_YEAR int,
   MOVIE_COST decimal(4,2),
   MOVIE_GENRE varchar(10),
   PRICE_CODE int,
   PRIMARY KEY(MOVIE_NUM),
   FOREIGN KEY (PRICE_CODE) REFERENCES PRICE(PRICE_CODE),
);

CREATE TABLE VIDEO
(
  VID_NUM varchar(5),
  VID_INDATE date,
  MOVIE_NUM varchar(4),
  PRIMARY KEY(VID_NUM),
  FOREIGN KEY (MOVIE_NUM) REFERENCES MOVIE(MOVIE_NUM)
);


Create TABLE DETAILRENTAL
(
RENT_NUM varchar(4),
VID_NUM varchar(5),
DETAIL_FEE decimal(2,1),
DETAIL_DUEDATE date,
DETAIL_RETURNDATE    date,
DETAIL_DAILYLATEFEE int,
PRIMARY KEY(RENT_NUM,VID_NUM),
FOREIGN KEY(RENT_NUM) REFERENCES RENTAL(RENT_NUM),
FOREIGN KEY(VID_NUM) REFERENCES VIDEO(VID_NUM),
);


INSERT INTO MEMBERSHIP VALUES('102','Tami','Dawson','2632 Takli Circle','Norene','TN','37136',11);
INSERT INTO MEMBERSHIP VALUES('103','Curt','Kinght','4035 Cornell Court','Flatgap','KY','41219',6);
INSERT INTO MEMBERSHIP VALUES('104','Jamal','Melendez','788 East 145th Avanus','Quebek','TN','38579',0);
INSERT INTO MEMBERSHIP VALUES('105','Iva','Mcclain','6045 Musket Ball Circle ','Summit','KY','42783',15);
INSERT INTO MEMBERSHIP VALUES('106','Miranda','Parks','4469 Maxwell Place','Germantown','TN','38183',0);
INSERT INTO MEMBERSHIP VALUES('107','Rosaio','Eliott','7578 Danner Avenue','Columbia','TN','38402',5);
INSERT INTO MEMBERSHIP VALUES('108','Mattie','Guy','4390 Evergreen Street','Lily','KY','40740',0);
INSERT INTO MEMBERSHIP VALUES('109','Clint','Ochoa','1711 Elm Street','Greeneville','TN','37745',10);
INSERT INTO MEMBERSHIP VALUES('110','Lewis','Rosales','4524 Southwild Circle','Counce','TN','3825',0);
INSERT INTO MEMBERSHIP VALUES('111','Stacy','Mann','2789 East Cook Avanue','Murfreesboro','TN','37132',8);
INSERT INTO MEMBERSHIP VALUES('112','Luis','Trujilo','7267 Melvin Avenue','Heiskell','TN','37754',3);
INSERT INTO MEMBERSHIP VALUES('113','Minnie','Gonzaies','6430 Vasli Drive','Willston','TN','38076',0);

INSERT INTO RENTAL VALUES('1001','01-MAR-11','103');
INSERT INTO RENTAL VALUES('1002','01-MAR-11','105');
INSERT INTO RENTAL VALUES('1003','02-MAR-11','102');
INSERT INTO RENTAL VALUES('1004','02-MAR-11','110');
INSERT INTO RENTAL VALUES('1005','02-MAR-11','111');
INSERT INTO RENTAL VALUES('1006','02-MAR-11','107');
INSERT INTO RENTAL VALUES('1007','02-MAR-11','104');
INSERT INTO RENTAL VALUES('1008','03-MAR-11','105');
INSERT INTO RENTAL VALUES('1009','03-MAR-11','111');

INSERT INTO PRICE VALUES(1,'Stardard',2,1);
INSERT INTO PRICE VALUES(2,'New Release',3.5,3);
INSERT INTO PRICE VALUES(3,'Discount',1.5,1);
INSERT INTO PRICE VALUES(4,'Weekly Special',1,0.5);


INSERT INTO MOVIE VALUES ('1234','The Cesar Family Chrismas',2009,39.95,'FAMILY',2);
INSERT INTO MOVIE VALUES ('1235','Smokey Mountain Wildlife',2006,59.95,'ACTION',1);
INSERT INTO MOVIE VALUES ('1236','Richard Goodhope',2010,59.95,'DRAMA',2);
INSERT INTO MOVIE VALUES ('1237','Beatnik Fever',2009,29.95,'COMEDY',2);
INSERT INTO MOVIE VALUES ('1238','Constant Companion',2010,89.95,'DRAMA',2);
INSERT INTO MOVIE VALUES ('1239','Where Hope Dies',2000,25.49,'DRAMA',3);
INSERT INTO MOVIE VALUES ('1245','Time To Burn',2007,45.49,'ACTION',1);
INSERT INTO MOVIE VALUES ('1246','What He Doesn not know',2008,58.29,'COMEDY',1);


INSERT INTO VIDEO VALUES ('54321','18-JUN-10','1234');
INSERT INTO VIDEO VALUES ('54324','18-JUN-10','1234');
INSERT INTO VIDEO VALUES ('54325','18-JUN-10','1234');
INSERT INTO VIDEO VALUES ('34341','22-JUN-09','1235');
INSERT INTO VIDEO VALUES ('34342','22-JUN-09','1235');
INSERT INTO VIDEO VALUES ('34366','02-MAR-11','1236');
INSERT INTO VIDEO VALUES ('34367','02-MAR-11','1236');
INSERT INTO VIDEO VALUES ('34369','02-MAR-11','1236');
INSERT INTO VIDEO VALUES ('44392','02-OCT-10','1237');
INSERT INTO VIDEO VALUES ('44397','02-OCT-10','1237');
INSERT INTO VIDEO VALUES ('59237','14-FEB-11','1237');
INSERT INTO VIDEO VALUES ('61388','25-JAN-09','1239');
INSERT INTO VIDEO VALUES ('61353','28-JAN-08','1245');
INSERT INTO VIDEO VALUES ('61354','28-JAN-08','1245');
INSERT INTO VIDEO VALUES ('61367','30-JUL-10','1246');
INSERT INTO VIDEO VALUES ('61359','30-JUL-10','1246');


INSERT INTO DETAILRENTAL VALUES ('1001','34342',2,'04-MAR-11','02-MAR-11',NULL);
INSERT INTO DETAILRENTAL VALUES ('1001','61353',2,'04-MAR-11','03-MAR-11',1);
INSERT INTO DETAILRENTAL VALUES ('1002','59237',3.5,'04-MAR-11','04-MAR-11',3);
INSERT INTO DETAILRENTAL VALUES ('1003','54325',3.5,'04-MAR-11','09-MAR-11',3);
***********INSERT INTO DETAILRENTAL VALUES ('1003','61369',2,'06-MAR-11','09-MAR-11',1);

-----------------------


error คือ

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__DETAILREN__VID_N__1ED998B2". The conflict occurred in database "TINYVIDEO", table "dbo.VIDEO", column 'VID_NUM'.
The statement has been terminated.

ช่วยด้วยค้า YY