Use a foreign key just to prevent data entry? SQL
When an insert is performed on a table, the foreign key is automatically checked against its parent table to ensure that the value exists. If it doesn't exist, insert doesn't work.
My question is, is it wrong to set a value as a foreign key just to validate the data on insert?
Some insightful context of what I am trying to do:
I am building a fight base to brush up on anything I forgot after college. As a wrestling fan, I thought to use it as the topic will keep my interest (and it is).
Create Table Superstar
(
Superstar_ID Int Not Null Primary Key Identity (100,1),
Employee_ID Int Not Null Foreign Key References Employee(Employee_ID),
Alignment_ID Int Not Null Foreign Key References Alignment(Alignment_ID),
SuperStar_Name Varchar(50) Not Null,
SuperStar_Weight Varchar(50) Not Null,
SuperStar_Height Varchar(50) Not Null,
Billed_From Varchar(50) Not Null,
Active Bit Not Null
)
Create Table Title
(
Title_ID Int Not Null Primary Key Identity(1,1),
Title_Name Varchar(50) Not Null,
Title_Description Varchar(50) Not Null,
**Current_Holder Int Foreign Key References Superstar(Superstar_ID),
Former_Holder Int Foreign Key References Superstar(Superstar_ID),**
Active Bit Not Null
)
Create Table Superstar_Title
(
Superstar_Title_ID Int Not Null Primary Key Identity (1,1),
Title_ID Int Not Null Foreign Key References Title(Title_ID),
Superstar_ID Int Not Null Foreign Key References Superstar(Superstar_ID)
)
In the Header table, I set the Current_Holder and Former_Holder fields as foreign keys to check the Superstar table that the Superstar_ID exists on the insert.
Is this terribly wrong? Using foreign key just for inline validation constraint?
source to share
That the whole point of having a constraint is to check that the data is consistent. So, no, that's fine. In fact, it was encouraging - it preserves the integrity of the data so that you don't get things that are "held" by non-existent people.
Edit . What I mean is that “encouraged” is that while you must do it, technology allows you not to. If you want to check manually, then you are free to not enforce foreign key relationships in the database. However, you have to check somewhere, or else, you end up with useless data. And since you are combing your SQL skills, I think it is best to use all the RDMBS features that you use.
source to share