I have created several tables in MS SQL Server 2012. The problem is when I go to
ID: 641667 • Letter: I
Question
I have created several tables in MS SQL Server 2012. The problem is when I go to insert the data to the tables, I get errors and I can't figure out why.
Here's the SQL for the table creation:
CREATE TABLE EMPLOYEE (
SSN Varchar(11) NOT NULL,
Name Char(50) NOT NULL,
Phone Varchar(11) NULL,
UnionMembershipNumber Int NULL,
CONSTRAINT EmployeePK PRIMARY KEY(SSN)
);
CREATE TABLE TECHNICIAN (
SSN Varchar(11) NOT NULL,
Salary Smallmoney NOT NULL,
CONSTRAINT TechnicianPK PRIMARY KEY(SSN),
CONSTRAINT TechEmpFK FOREIGN KEY(SSN)
REFERENCES EMPLOYEE(SSN)
ON UPDATE NO ACTION
ON DELETE NO ACTION
);
CREATE TABLE TRAFFIC_CONTROLLER (
SSN Varchar(11) NOT NULL,
DateOfTraining Datetime NULL,
CONSTRAINT Traffic_ControllerPK PRIMARY KEY(SSN),
CONSTRAINT TCEmpFK FOREIGN KEY(SSN)
REFERENCES EMPLOYEE(SSN)
ON UPDATE NO ACTION
ON DELETE NO ACTION
);
CREATE TABLE MODEL (
ModelNumber Varchar(20) NOT NULL,
Capacity Numeric(3) NOT NULL,
MaxRange Numeric(5) NULL,
CONSTRAINT ModelPK PRIMARY KEY(ModelNumber)
);
CREATE TABLE AIRPLANE (
RegNumber Varchar(6) NOT NULL,
ModelNumber Varchar(20) NOT NULL,
CONSTRAINT AirplanePK PRIMARY KEY(RegNumber),
CONSTRAINT APModelFK FOREIGN KEY(ModelNumber)
REFERENCES MODEL(ModelNumber)
ON UPDATE NO ACTION
ON DELETE NO ACTION
);
CREATE TABLE EXPERT (
SSN Varchar(11) NOT NULL,
ModelNumber Varchar(20) NOT NULL,
CONSTRAINT ExpertPK PRIMARY KEY(SSN),
CONSTRAINT ExpertModelFK FOREIGN KEY(ModelNumber)
REFERENCES MODEL(ModelNumber)
ON UPDATE NO ACTION
ON DELETE NO ACTION,
CONSTRAINT ExpertTechFK FOREIGN KEY(SSN)
REFERENCES TECHNICIAN(SSN)
ON UPDATE NO ACTION
ON DELETE NO ACTION
);
CREATE TABLE TEST (
TestNum Numeric(3) NOT NULL,
Name Char(50) NOT NULL,
MaxScore Numeric(2) NOT NULL,
CONSTRAINT TestPK PRIMARY KEY(TestNum),
);
CREATE TABLE TEST_EVENTS (
RegNumber Varchar(6) NOT NULL,
TestNum Numeric(3) NOT NULL,
SSN Varchar(11) NOT NULL,
[Date] Datetime NOT NULL,
Score Numeric(2) NOT NULL,
CONSTRAINT Test_EventsPK PRIMARY KEY(RegNumber, TestNum, SSN, [Date]),
CONSTRAINT TestAirplaneFK FOREIGN KEY(RegNumber)
REFERENCES AIRPLANE(RegNumber)
ON UPDATE NO ACTION
ON DELETE NO ACTION,
CONSTRAINT TestFK FOREIGN KEY(TestNum)
REFERENCES TEST(TestNum)
ON UPDATE NO ACTION
ON DELETE NO ACTION,
CONSTRAINT TestTechFK FOREIGN KEY(SSN)
REFERENCES TECHNICIAN(SSN)
ON UPDATE NO ACTION
ON DELETE NO ACTION
);
Here is the SQL that I've written to try to insert the data into the above tables.
/* Insert data into Employee table */
INSERT INTO EMPLOYEE
(SSN, Name, Phone, UnionMembershipNumber)
VALUES ('558-26-8891', 'Jim Smith', '555-556-8895', 22555663);
INSERT INTO EMPLOYEE
(SSN, Name, Phone, UnionMembershipNumber)
VALUES ('558-99-4593','Pamela Rogers','555-556-8895', 12345678);
INSERT INTO EMPLOYEE
(SSN, Name, Phone, UnionMembershipNumber)
VALUES ('558-34-4852', 'Robert Jones', '555-556-8895', 53218891);
INSERT INTO EMPLOYEE
(SSN, Name, Phone, UnionMembershipNumber)
VALUES ('558-41-9632', 'Mia Lemmons', '555-556-8895',98745612);
INSERT INTO EMPLOYEE
(SSN, Name, Phone, UnionMembershipNumber)
VALUES ('558-17-6547', 'Sean Wright', '555-556-8895',85236987);
INSERT INTO EMPLOYEE
(SSN, Name, Phone, UnionMembershipNumber)
VALUES ('558-39-1234', 'William Reed', '555-556-8895', 15983574);
/* Insert data into Technician table */
INSERT INTO TECHNICIAN
(SSN, Salary)
VALUES ('558-26-8891', 58000);
INSERT INTO TECHNICIAN
(SSN, Salary)
VALUES ('558-99-4593', 62000);
INSERT INTO TECHNICIAN
(SSN, Salary)
VALUES ('558-34-4852', 68000);
/* Insert data into Traffic Controller table */
INSERT INTO TRAFFIC_CONTROLLER
(SSN, DateOfTraining)
VALUES ('558-41-9632', '1/1/2015');
INSERT INTO TRAFFIC_CONTROLLER
(SSN, DateOfTraining)
VALUES ('558176547', '5/1/2015');
INSERT INTO TRAFFIC_CONTROLLER
(SSN, DateOfTraining)
VALUES ('558391234', '1/15/2015');
/* Insert data into Model table */
INSERT INTO MODEL
(ModelNumber, Capacity, MaxRange)
VALUES ('Boeing 787', 242, 15700);
INSERT INTO MODEL
(ModelNumber, Capacity, MaxRange)
VALUES ('Airbus A380', 700, 15000);
INSERT INTO MODEL
(ModelNumber, Capacity, MaxRange)
VALUES ('Boeing 737-900', 179, 5080);
/* Insert data into Airplane table */
INSERT INTO AIRPLANE
(RegNumber, ModelNumber)
VALUES ('N1234G', 'Boeing 787');
INSERT INTO AIRPLANE
(RegNumber, ModelNumber)
VALUES ('N1667H', 'Airbus A380');
INSERT INTO AIRPLANE
(RegNumber, ModelNumber)
VALUES ('NJ7760', 'Boeing 737-900');
/* Insert data into Expert table */
INSERT INTO EXPERT
(SSN, ModelNumber)
VALUES ('558268891', 'Boeing 787');
INSERT INTO EXPERT
(SSN, ModelNumber)
VALUES ('558994593', 'Airbus A380');
INSERT INTO EXPERT
(SSN, ModelNumber)
VALUES ('558344852', 'Boeing 737-900');
/* Insert data into Test table */
INSERT INTO TEST
(TestNum, Name, MaxScore)
VALUES (100, 'Brakes', 99);
INSERT INTO TEST
(TestNum, Name, MaxScore)
VALUES (200, 'Altimeter', 99);
INSERT INTO TEST
(TestNum, Name, MaxScore)
VALUES (300, 'Nosegear', 99);
/* Insert data into Test Events table */
INSERT INTO TEST_EVENTS
(RegNumber, TestNum, SSN, Date, Score)
VALUES ('N1234G',100, '558268891', '3/12/2014', 96);
INSERT INTO TEST_EVENTS
(RegNumber, TestNum, SSN, Date, Score)
VALUES ('N1667H',200, '558994593', '6/01/2015', 89);
INSERT INTO TEST_EVENTS
(RegNumber, TestNum, SSN, Date, Score)
VALUES ('NJ7760',300, '558344852', '9/24/2014', 90);
These are the errors I receive:
Msg 8152, Level 16, State 14, Line 12
String or binary data would be truncated.
The statement has been terminated.
Msg 8152, Level 16, State 14, Line 16
String or binary data would be truncated.
The statement has been terminated.
Msg 8152, Level 16, State 14, Line 20
String or binary data would be truncated.
The statement has been terminated.
Msg 8152, Level 16, State 14, Line 24
String or binary data would be truncated.
The statement has been terminated.
Msg 8152, Level 16, State 14, Line 28
String or binary data would be truncated.
The statement has been terminated.
Msg 8152, Level 16, State 14, Line 32
String or binary data would be truncated.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 38
The INSERT statement conflicted with the FOREIGN KEY constraint "TechEmpFK". The conflict occurred in database "jjackson103", table "dbo.EMPLOYEE", column 'SSN'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 42
The INSERT statement conflicted with the FOREIGN KEY constraint "TechEmpFK". The conflict occurred in database "jjackson103", table "dbo.EMPLOYEE", column 'SSN'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 46
The INSERT statement conflicted with the FOREIGN KEY constraint "TechEmpFK". The conflict occurred in database "jjackson103", table "dbo.EMPLOYEE", column 'SSN'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 52
The INSERT statement conflicted with the FOREIGN KEY constraint "TCEmpFK". The conflict occurred in database "jjackson103", table "dbo.EMPLOYEE", column 'SSN'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 56
The INSERT statement conflicted with the FOREIGN KEY constraint "TCEmpFK". The conflict occurred in database "jjackson103", table "dbo.EMPLOYEE", column 'SSN'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 60
The INSERT statement conflicted with the FOREIGN KEY constraint "TCEmpFK". The conflict occurred in database "jjackson103", table "dbo.EMPLOYEE", column 'SSN'.
The statement has been terminated.
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
Msg 547, Level 16, State 0, Line 92
The INSERT statement conflicted with the FOREIGN KEY constraint "ExpertTechFK". The conflict occurred in database "jjackson103", table "dbo.TECHNICIAN", column 'SSN'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 96
The INSERT statement conflicted with the FOREIGN KEY constraint "ExpertTechFK". The conflict occurred in database "jjackson103", table "dbo.TECHNICIAN", column 'SSN'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 100
The INSERT statement conflicted with the FOREIGN KEY constraint "ExpertTechFK". The conflict occurred in database "jjackson103", table "dbo.TECHNICIAN", column 'SSN'.
The statement has been terminated.
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
Msg 547, Level 16, State 0, Line 120
The INSERT statement conflicted with the FOREIGN KEY constraint "TestTechFK". The conflict occurred in database "jjackson103", table "dbo.TECHNICIAN", column 'SSN'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 124
The INSERT statement conflicted with the FOREIGN KEY constraint "TestTechFK". The conflict occurred in database "jjackson103", table "dbo.TECHNICIAN", column 'SSN'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 128
The INSERT statement conflicted with the FOREIGN KEY constraint "TestTechFK". The conflict occurred in database "jjackson103", table "dbo.TECHNICIAN", column 'SSN'.
The statement has been terminated.
Explanation / Answer
you are using TechEmpFK as foreign key..foreign key must be a primary key and that is database rule..But i cant found anywhere TechEmpFK primary key..please check databse tables and schema
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.