FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


quick question on an sql query





davidv
Code:
CREATE TABLE Enrolled(
sid CHAR(20),
cid CHAR(20),
grade CHAR(2),
PRIMARY KEY (sid,cid),
FOREIGN KEY (sid)
REFERENCES Student
ON DELETE CASCADE
ON UPDATE SET DEFAULT )


Does the delete cascade on update set default just mean if a student is deleted, i.e. that sid no longer exists, i want all enrolled tuples to change the value of the sid fk to some default value? if that's the case, what is the default value? is default special like null?

is it possible to have that delete and update constraint in the students table or can it only be used with foreign keys? in other words, can i create a constraint in the students table where i update sid values of all enrolled tuples if a student is deleted.

also, just to double check:

does

Code:
constraint sid_fk foreign key (sid) references student


just mean i'm naming the constraint sid_fk and defining to be that the value of the sid must intersect with at least one primary key value in the student table?

same as saying:

Code:
sid_fk
foreign key(sid_fk) references student

except we aren't naming the constraint, the dbms is?

thanks.
Fire Boar
Basically, you're defining what should happen in the case of what would be a foreign key violation. ON DELETE and ON UPDATE are the two events: ON DELETE happens when the row being referred to (in this case the row in Student with matching sid) is deleted. ON UPDATE happens when you change the primary key without deleting the row.

CASCADE means "propagate changes". So in the case of a delete, any row which would violate the foreign key constraint is deleted too. In the case of an update, updating a key referred to will also update all references to it. CASCADE is useful for when you have data which strongly depends on some other entity, and the absence of that entity makes that data redundant. For example, if you delete a student from your records, you may want to CASCADE to automatically delete all enrollment data for that student.

SET DEFAULT fixes the violation by setting any rows which would violate the constraint to some default value. The value used is:
- Whatever default you specified by the DEFAULT clause in table definition, if you specified one.
- NULL if the field is not a NOT NULL field.
- 0, or "", or some other empty value depending on the data type.

SET NULL is like SET DEFAULT, but always uses NULL. SET NULL cannot be used for NOT NULL fields. SET NULL or SET DEFAULT are useful when you want to keep data even after some associated data is deleted, for example, if you delete a blog user, you can set all posts by that user to have a null user ID, so that they are attributed to an anonymous author.


And yes, in answer to your question, omitting CONSTRAINT just means the DBMS names it for you. There's no other semantic difference.
davidv
Thanks! That was really helpful.
Related topics
Quick Cpanel Question
SQL to XML converter
Windows Xp 64
sql with phpmyadmin
MySql to SQL
SQL query problem
mySQL Query/PHP code - get the highest value...
Web security testing
Debug my SQL query please
I got a question about Mobile and MSN.
Question Concerning Every Markup Language
Gmail new feature and a question
Noob question
the best way to add a search function is?
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.