I have created two tables and also created a relationship between them.
Table students
:
create table students
(
[StudentId] NVARCHAR(50) NOT NULL PRIMARY KEY,
[Name] NVARCHAR(50) NOT NULL
);
Table studentprofile
:
create table studentprofile
(
[Id] INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
[StudentId] NVARCHAR(50) NOT NULL,
[Address] NVARCHAR(50) NOT NULL,
);
and relationship:
alter table studentprofile
add constraint students_studentprofile_FK
foreign key (StudentId)
references students(StudentId)
on delete cascade on update cascade
But, when I wrote this line:
insert into students values('110111', 'Marik')
the value of StudentId
(in table studentprofile
) wasn't updated automatically. Why?
Can you tell me how to set the value of StudentId
(in table studentprofile
) can be inserted automatically whenever I insert into table students
?
There is no such thing as
insert cascade
.You can implement such a thing by using a trigger for insert on your
students
table, inserting default values (or nulls) into thestudentprofile
table:Note that your Address column is defined as not null and has no default value, this is why I've used the hard coded
'NO ADDRESS'
for it.However, I agree with the comments on your question: you would be better off inserting the data to the student profile using a different insert statement (perhaps inside a transaction with the insert to students).