Click here to Skip to main content
14,494,281 members
Rate this:
Please Sign up or sign in to vote.
Create database Uzina

use Uzina
if(object_id('tUzina') is not null)
drop table tUzina
if(object_id('tDepartamente') is not null)
drop table tDepartamente
if(object_id('tSpecializari') is not null)
drop table tSpecializari
if(object_id('tAngajati') is not null)
drop table tAngajati
if(object_id('tAtributii') is not null)
drop table tAtributii
if(object_id('tSalarii') is not null)
drop table tSalarii

create schema Dacia

create table tUzina 
(codUzi char(10) constraint PK_Uzina primary key, 
denumire varchar(50),
adresa varchar(50))

create table tDepartamente 
(codDep char(10) constraint PK_Departamente primary key,
denumire varchar(50),
codUzi char(10) constraint FK_Uzi foreign key references tUzina)

create table tSpecializari
(codSpec char(10) constraint PK_Specializari primary key,
denumire varchar(50),
codDep char(10) constraint FK_Dep foreign key references tDepartamente)

create table tAngajati
(codAng char(10) constraint PK_Angajati primary key,
nume varchar(50),
CNP char(13),
codSpec char(10) constraint FK_Spec foreign key references tSpecializari,
codJud char(10),
localitate varchar(20))

create table tAtributii
(codAtr char(10) constraint PK_Atributii primary key,
denumire varchar(50),
tipAtributii varchar(50) not null constraint CK_tipAtributii check(tipAtributii in('B','0','F')),
)

create table tSalariu
(codSal char(10) constraint PK_Salariu primary key,
codAng char(10) constraint FK_Ang foreign key references tAngajati,
dataSalariu tinyint,
salariu char(10))

alter schema Dacia transfer dbo.tUzina
alter schema Dacia transfer dbo.tSpecializari
alter schema Dacia transfer dbo.tSalariu
alter schema Dacia transfer dbo.tDepartamente
alter schema Dacia transfer dbo.tAtributii
alter schema Dacia transfer dbo.tAngajati
go



create synonym tUzina for Dacia.tUzina
create synonym tSpecializari for Dacia.tSpecializari
create synonym tSalariu for Dacia.tSalariu
create synonym tDepartamente for Dacia.tDepartamente
create synonym tAtributii for Dacia.tAtributii
create synonym tAngajati for Dacia.tAngajati

insert into tUzina (codUzi,denumire,adresa)
values ('DPPI','Departamentul de Perfectionare al Inginerilor','adresa'),
		('RDPT','Reprezentanta Delta Plus Trading','adresa'),
		('RDPA','Reprezentanta Daperom Grup Auto','adresa'),
		('RA','Reprezentanta Amat','adresa'),
		('RAD','Reprezentanta Auto Dacia S.A.','adresa'),
		('UDM','Uzina Dacia Mioveni','adresa')

alter table Dacia.tUzina
alter column denumire varchar(80) not null

select * from tUzina
select * from tAngajati
select * from tDepartamente
select * from tSpecializari

/*
codUzina	    denumire	                                       adresa
DPPI    	Departamentul de Perfectionare al Inginerilor			NULL
RDPT      	Reprezentanta Delta Plus Trading						NULL
RDPA       	Reprezentanta Daperom Grup Auto							NULL
RA      	Reprezentanta Amat										NULL
RAD			Reprezentanta Auto Dacia S.A.							NULL
UDM			Uzina Dacia Mioveni										NULL
*/


insert into tDepartamente(codDep, denumire, codUzi)
values ('DC','Caroserii','UDM'),
       ('DM','Montaj','UDM'),
	   ('DF','Fabricatie','UDM'),
	   

insert into tSpecializari(codSpec,denumire,codDep)
values	('CRS','Caroserii','UDM'),
		('MTJ','Montaj','UDM'),
		('FAB','Fabricatie','UDM')

insert into tAngajati(codAng,nume,CNP,codSpec,codJud,localitate)
values 
('A01','Geani', '1990514123456', 'CRS', 'AG', 'Pitesti'),
('A02','Mirel', '1990514123457', 'MTJ', 'AG', 'Pitesti'),
('A03', 'Ionelus', '1990514123458', 'MTJ', 'AG', 'C.de AG'),
('A04', 'Horatiu', '1990514123459', 'CRS', 'AG', 'Mioveni'),
('A05', 'Sorinel', '1990514123452', 'FAB', 'AG', 'Mioveni'),
('A06', 'Camasuta', '2194434123346', 'CRS', 'AG', 'Topoloveni')


Msg 547, Level 16, State 0, Line 109
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Spec". The conflict occurred in database "Uzina", table "Dacia.tSpecializari", column 'codSpec'.
The statement has been terminated.


What I have tried:

help me pls
i tried to execute again "use Uzina" and that s all. idk what i should do
Posted
Updated 25-Mar-20 4:54am
Rate this:
Please Sign up or sign in to vote.

Solution 1

You get quicker answers if you tell us exactly where the problem occurs. See
insert into tSpecializari(codSpec,denumire,codDep)
values	('CRS','Caroserii','UDM'),
		('MTJ','Montaj','UDM'),
		('FAB','Fabricatie','UDM')
You have set up a constraint that means the codDep must already exist on table tDepartamente before you attempt to refer to it in the tSpecializari table. It doesn't appear in this code
insert into tDepartamente(codDep, denumire, codUzi)
values ('DC','Caroserii','UDM'),
       ('DM','Montaj','UDM'),
	   ('DF','Fabricatie','UDM')
   
Comments
Member 14782613 25-Mar-20 10:49am
   
the problem occours when i try to execute tSpecializari, i executed tDepartamente and it works. also tAngajati don t work
CHill60 25-Mar-20 13:00pm
   
You have inserted some values into tDepartamente yes. And it worked. However, the values for codDep that you are trying to insert into tSpecializari are not the ones you inserted into codDep in tDepartamente. The values have to exist in tDepartamente first before you can use them in any other table that is constrained to use existing values
Rate this:
Please Sign up or sign in to vote.

Solution 2

/*
Msg 547, Level 16, State 0, Line 109
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Spec".
The conflict occurred in database "Uzina", table "Dacia.tSpecializari", column 'codSpec'.
The statement has been terminated.
*/
What this means is that a Foreign Key constraint would be violated. The name of the constraint is FK_Spec and is found in this table definition
CREATE TABLE tAngajati (
   codAng     char(10)   constraint PK_Angajati primary key,
   nume       varchar(50),
   CNP        char(13),
   codSpec    char(10)   constraint FK_Spec foreign key references tSpecializari,
   codJud     char(10),
   localitate varchar(20)
)
And the definition seen here says that the value for codSpec MUST EXIST in the tSpecializari table before it can be inserted into this table
CREATE TABLE tSpecializari (
   codSpec    char(10)     constraint PK_Specializari primary key,
   denumire   varchar(50),
   codDep     char(10)     constraint FK_Dep foreign key references tDepartamente
)
And likewise this table requires that codDep exists in the tDepartament table before insertion can occur
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100