Databases
StackOverflow2013
Postsdbo
POHow to avoid bad data entries in table through proper Key relationship?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to avoid bad data entries in table through proper Key relationship?
    text
    copied!<p>I have 3 tables. widgets, widget types, widget type ID. I have created direct relationship between them. How can I avoid bad data going into Widget tables based on <code>Widget_type</code> and <code>Widget_sub_Type</code>. Please see my code. There is just one small thing missing.</p> <pre><code>Create table widgets ( Widget_ID int not null primary key, Widget_type_ID int not null, Widget_Sub_type_ID int, Widget_Name varchar (50) ) Create table Widget_Type ( Widget_Type_ID int not null primary key, ) Create table Widget_Sub_type ( Widget_Sub_Type_ID int not null primary key, Widget_Type_ID int not null ) ---adding foregin key constraints Alter table widgets ADD constraint FK_Widget_Type FOREIGN KEY (Widget_type_ID) References Widget_Type (Widget_type_ID) Alter table widgets ADD constraint FK_Widget_Sub_Type FOREIGN KEY (Widget_Sub_type_ID) References Widget_SUB_Type (Widget_SUB_type_ID) Alter table widget_Sub_Type ADD Constraint FK_Widget_Type_Alter Foreign key (widget_Type_ID) References Widget_Type (Widget_Type_ID) ---- insert values insert Widget_Type values (1) insert Widget_Type values (5) insert Widget_Sub_type values (3,1) insert Widget_Sub_type values (4,1) insert Widget_Sub_type values (7,5) insert Widget_Sub_type values (9,5) -- This will error out which is correct insert Widget_Sub_type values (5,6) select * from Widget_Sub_type select * from Widget_type --Good insert widgets (Widget_ID,Widget_Name, Widget_type_ID, Widget_Sub_type_ID) values (1, 'TOY', 1, 3) select * from widgets --Good insert widgets (Widget_ID,Widget_Name, Widget_type_ID, Widget_Sub_type_ID) values (2, 'BatMan', 5, 7) -- How to prevenet this, 3 is not sub_type_id of type_ID 5. This is bad data, It should not be inserted. insert widgets (Widget_ID,Widget_Name, Widget_type_ID, Widget_Sub_type_ID) values (3, 'Should Not', 5, 3) </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload