SQL Create Table quest. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Create Table quest.

Author

Topic

snowy
Starting Member

5 Posts

Posted - 2009-09-25 : 09:53:38

hey, I'm pretty new to this SQL stuff
and i'm just trying 2 create a table with constraints
but I saw this example of creating a table with a line
that i dont' get and i can't find anything similar that explains what it actually does, here it is.

CREATE TALBE student(
  sno CHAR(8) NOT NULL,
  name CHAR(40) NOT NULL,
  supervisor CHAR(8) NOT NULL,
  PRIMARY KEY(supervisor)
    REFERENCES staff(id)
    ON UPDATE CASCADE
    ON DELETE SET DEFAULT);

What does ON DELETE SET DEFAULT actually do?

Question 2:
Using the above example, say I got a staff table and
as specified above supervisor references staff(id)
I want to make it such that if i delete a member of staff
it will delete the corresponding record in student, do
I just write ON DELETE CASCADE after the line "REFERENCES staff(id)" ?

Thanx

craigwg
Posting Yak Master

154 Posts

Posted - 2009-09-25 : 10:18:00

As for your first question, I took the following paragraph from http://technet.microsoft.com/en-us/library/ms186973.aspx, which also describes your other options.

ON DELETE SET DEFAULT
Specifies that if an attempt is made to delete a row with a key referenced by foreign keys in existing rows in other tables, all the values that make up the foreign key in the rows that are referenced are set to their default value. All foreign key columns of the target table must have a default definition for this constraint to execute. If a column is nullable, and there is no explicit default value set, NULL becomes the implicit default value of the column. Any nonnull values that are set because of ON DELETE SET DEFAULT must have corresponding values in the primary table to maintain the validity of the foreign key constraint.

To make use of this you would want to insure your foriegn keys have an appropriate default. If you plan on deleting such fields though, it might be better to rethink your database design if possible. If not, this might be a good idea after all!

As for your second question, here is the definition based on the same site above:

ON DELETE CASCADE
Specifies that if an attempt is made to delete a row with a key referenced by foreign keys in existing rows in other tables, all rows that contain those foreign keys are also deleted.

So the short answer to your question is "YES".

Hope that helps!
Craig

Craig Greenwood

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources