[N00b] Learning SQL from scratch, have a question...

Oink

I'm teaching myself SQL pretty much from scratch, and I'm struggling with the following. If any good soul out there has time to help a n00b, I'd appreciate it...

In order to practice using foreign keys, I want to create 2 tables called Parent and Child. The child table has a ParentID column, which should get its value ('ParentX') from the Parent table using a FK. This is not happening, and I don't know why.I googled it but the examples I found were rather opaque and I obviously did not get it...

Any help very much appreciated!! Thanks!!

Here's the offending code:

use [DBNAME]
create table parent
(
ParentId nvarchar(20) NOT NULL,
Parentdata nvarchar(20) NOT NULL
constraint parentPK PRIMARY KEY (ParentId)
)
;
use [DBNAME]
create table child
(
ChildId nvarchar(20) NOT NULL,
Childdata nvarchar(20) NULL,
ParentID nvarchar(20) NULL
constraint childPK PRIMARY KEY (ChildId)
constraint childFK foreign key references Parent(ParentId) on delete cascade on update cascade
)
;
use [DBNAME]
insert into child (childid, Childdata)
values ('childX', 'childata')
insert into parent (parentid, parentdata)
values ('parentX', 'pdata')
select * from parent
select * from child
;

chrislad

Here's an example of something I'm working in. I'm just using MySQL workbench to create the tables.

CREATE TABLE IF NOT EXISTS `mtc`.`users` (
`username` VARCHAR(45) NOT NULL,
`name` VARCHAR(45) NULL DEFAULT NULL,
`password` VARCHAR(45) NULL DEFAULT NULL,
`gender` VARCHAR(45) NULL DEFAULT NULL,
`member_type` VARCHAR(45) NULL DEFAULT NULL,
`grade` VARCHAR(45) NULL DEFAULT NULL,
`ad_line1` VARCHAR(45) NULL DEFAULT NULL,
`ad_line2` VARCHAR(45) NULL DEFAULT NULL,
`ad_city` VARCHAR(45) NULL DEFAULT NULL,
`ad_county` VARCHAR(45) NULL DEFAULT NULL,
`contact_num` VARCHAR(45) NULL DEFAULT NULL,
`em_con_name` VARCHAR(45) NULL DEFAULT NULL,
`em_con_num` VARCHAR(45) NULL DEFAULT NULL,
PRIMARY KEY (`username`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

Only difference my untrained eyes can see are that your column names aren't in ' ', but I don't think that should matter.

Colonel Panic

You need explicitly set the parent id in the child record to be the id of a record in the parent table.

The foreign key constaint you've set up here means that the child's parent id must be in the parent table or be null and if you delete a parent that has children, the children get deleted too.

Oink

Colonel Panic wrote: »

You need explicitly set the parent id in the child record to be the id of a record in the parent table.

The foreign key constaint you've set up here means that the child's parent id must be in the parent table or be null and if you delete a parent that has children, the children get deleted too.

I think I know what you mean, but let me check:
I need to enter 'ParentId' MANUALLY in the child table.
When I delete the parent, the child is deleted automatically (tried it after adding the parentid, it worked). Is this the only expected result with the query I wrote? I thought it would populate the 'ParentId' automatically, rather than link a manually entered record. Can you please confirm?

If so, I can move on to the next topic! Thanks very much, I've been struggling with this since yesterday!!

seamus

No, you have to insert the parent ID yourself - otherwise how does the database know what parent id to assign to a row?

Note that the behaviour to delete the child rows when you delete the parent is not default. You told it to do that yourself in the below statement;

constraint childFK foreign key references Parent(ParentId) [b]on delete cascade[/b] on update cascade

Typically if you just create a foreign key constraint and don't specify the delete and update actions, you won't be able to enter a parent ID in the child table if it doesn't exist, but you also won't be able to delete any rows from the parent table while there are rows in the child table which contain the parent IDs you're trying to delete.

Colonel Panic

I can confirm that FK constraints don't auto populate like that. What if you have multiple parent records and added a child? Which parent id would it use?

You also specified that on update, the changes cascade. Sometimes this is desirable but in your case, why would you change the id of a parent record?

Oink

@Colonel Panic: that makes sense...

Ok, so I think I now understand the basic principle of FKs.

However if I want the ChildData record for a specified ChildId to populate in the parent table and update automatically, do I use FKs, or is it something else? I don't mind researching the details myself but you might point me the general direction :-)...

Cheers!

cython

Oink wrote: »

@Colonel Panic: that makes sense...

Ok, so I think I now understand the basic principle of FKs.

However if I want the ChildData record for a specified ChildId to populate in the parent table and update automatically, do I use FKs, or is it something else? I don't mind researching the details myself but you might point me the general direction :-)...

Cheers!

As already stated, when you create a record in the child table, you need to specify a parentId for that record. If you attempt an insert to child with a parentId that does not exist in the parent then you should get a constraint violation (which would throw an exception if done programmatically through something like Java).

In terms of populating the parent table "automatically" in response to the above scenario, I'm not sure that's doable natively in most SQL databases. Besides, you should really have whatever application is creating the child record either check for the existence of the parent, or handle the generated exception (from the constraint violation) in such a way that it has a similar effect, and then create the missing parent if it has sufficient data to do so. In plenty of scenarios, though, there would not be an appropriate set of default values to simply create the parent record in response to a child referencing a non-existent parent, so to an extent I have to question what you're actually trying to achieve here?

Oink

cython wrote: »

As already stated, when you create a record in the child table, you need to specify a parentId for that record. If you attempt an insert to child with a parentId that does not exist in the parent then you should get a constraint violation (which would throw an exception if done programmatically through something like Java).

In terms of populating the parent table "automatically" in response to the above scenario, I'm not sure that's doable natively in most SQL databases. Besides, you should really have whatever application is creating the child record either check for the existence of the parent, or handle the generated exception (from the constraint violation) in such a way that it has a similar effect, and then create the missing parent if it has sufficient data to do so. In plenty of scenarios, though, there would not be an appropriate set of default values to simply create the parent record in response to a child referencing a non-existent parent, so to an extent I have to question what you're actually trying to achieve here?

I think that answers the question. I am trying to learn what can be done with FKs and how, and this type of "link" is obviously not one of the options. So I can move on then .

Thanks for all the answers! (I can now tell my boss that I need Boards for work...)

Colonel Panic

Oink wrote: »

@Colonel Panic: that makes sense...

Ok, so I think I now understand the basic principle of FKs.

However if I want the ChildData record for a specified ChildId to populate in the parent table and update automatically, do I use FKs, or is it something else? I don't mind researching the details myself but you might point me the general direction :-)...

Cheers!

Depending on the flavour of SQL you're using, you could implement additional logic via triggers. Triggers can be tricky though. Most people only consider the case where there's one record being changed when you need to cater for the insert, update or delete of multiple records.

Similarly, you could use Stored Procedures to do it. Some people swear by having them as the sole interface to a database. I personally disagree and prefer to use constraints and triggers then handle the rest in client side code.

Oink

Ach so. I'll look into triggers and stored procedures next then. Back to my books...
Thanks!

Tom Dunne

Foreign keys are used to enforce rules or logic. When trying to understand topics such as FKs, it helps to relate it to the real world. Remember, the likes of FKs were created for a reason, and thinking about situations where you have a reason to use them should help to understand them.

When adding data to a database, ask yourself the question "What can exist on its own?" (i.e. not needing a FK, or not needing an entry in another table). Then ask "what cannot exist on its own?" As in, what data is dependent on other data?

For example, if we take a university enrollment system, simplified to courses, students and classes.

Can student data exist without classes? (your answer should be yes - think about it, during enrollment, for example)

Can course data exist without associated classes? (again, yes, a course might be on the system, but not running, hence no classes)

Can a class exists without a course? (no, a class must have an assigned course)

So, in the last case, where a class must have an associated course, we could use a FK in the class table that relates to a course in the courses table, thereby enforcing the rule that you cannot have a class without an associated course.

Vastly over-simplified example, but I hope it helps.