I have two tables, AUTHOR and PC. I have declared the primary key of PC as foreign key in AUTHOR. When I tried to create the AUTHOR table with foreign key ON UPDATE CASCADE, I got the missing keyword error. When I used ON DELETE CASCADE, it creates the AUTHOR table. I want to know whether I can use both ON DELETE and ON UPDATE in one table? If so, why am I getting the problem for ON UPDATE? Could you please clarify this?
Whether you can use both ON DELETE and ON UPDATE will depend entirely on the database system you're using, and whether it supports those features.
We get a lot of questions submitted here, to the SQL Ask The Experts section of this site, by people who don't specify which database system they're using. That's as it should be, and most of the time my answer uses Structured Query Language that will work in any database system.
Sometimes, the questions are from people using Microsoft's SQL Server database, who either assume that SQL means SQL Server, or perhaps don't realize that there are separate SQL Server Ask The Experts sections. If I can, I will answer the question, even using SQL Server proprietary extensions to SQL if necessary.
But in your case, ON DELETE and ON UPDATE are standard SQL. So all I can say, based on the information you've given, is this: if you're using SQL Server, both ON DELETE and ON UPDATE are supported, so the problem is likely due to a syntax error on your part. If you're using some other database, I cannot help if I don't know what it is.
For More Information
- Dozens more answers to tough SQL questions from Rudy Limeback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, SQL Server, DB2, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.