Cascade Delete Can Not Apply To Two Foreign Keys
Jul 31, 2004
I have a table that contains two foreign keys of two different tables. I want to build a relationship so that when either primary keys deleted in the two tables, the record in the table should be deleted. But, SQL Server does not allow me to save the relationship, it complains that the circling delete might exist. I do not know why, how can I solve this?
Table A:
ID
ProductID <foreign key>
CustomerID <foreign key>
Table Product
ProductID <primary key>
Table Customer
CustomerID <primary key>
I want to cascade delete the record in Table A when either the ProductID is deleted from Product table or the CustomerID is deleted from Customer table.
View 1 Replies
ADVERTISEMENT
Oct 23, 2004
Hello:
Need some serious help with this one...
Background:
Am working on completing an ORM that can not only handles CRUD actions -- but that can also updates the structure of a table transparently when the class defs change. Reason for this is that I can't get the SQL scripts that would work for updating a software on SqlServer to be portable to other DBMS systems. Doing it by code, rather than SQL batch has a chance of making cross-platform, updateable, software...
Anyway, because it needs to be cross-DBMS capable, the constraints are that the system used must work for the lowest common denominator....ie, a 'recipe' of steps that will work on all DBMS's.
The Problem:
There might be simpler ways to do this with SqlServer (all ears :-) - just in case I can't make it cross platform right now) but, with simplistic DBMS's (SqlLite, etc) there is no way to ALTER table once formed: one has to COPY the Table to a new TMP name, adding a Column in the process, then delete the original, then rename the TMP to the original name.
This appears possible in SqlServer too --...as long as there are no CASCADE operations.
Truncate table doesn't seem to be the solution, nor drop, as they all seem to trigger a Cascade delete in the Foreign Table.
So -- please correct me if I am wrong here -- it appears that the operations would be
along the lines of:
a) Remove the Foreign Key references
b) Copy the table structure, and make a new temp table, adding the column
c) Copy the data over
d) Add the FK relations, that used to be in the first table, to the new table
e) Delete the original
f) Done?
The questions are:
a) How does one alter a table to REMOVE the Foreign Key References part, if it has no 'name'.
b) Anyone know of a good clean way to get, and save these constraints to reapply them to the new table. Hopefully with some cross platform ADO.NET solution? GetSchema etc appears to me to be very dbms dependant?
c) ANY and all tips on things I might run into later that I have not mentioned, are also greatly appreciated.
Thanks!
Sky
View 1 Replies
View Related
Apr 29, 2015
I have an issue where i am mandated to enforce RI on an applications database (a good thing). but I have several common lookup tables where many of the "codes" reside for many different code types. I also have the mandate that i cannot change the underlying DDL to make composite keys to match the codes table PK. I am currently looking at creating indexed views on top of the Codes table to seperate the logical tables it contains. This is several hundred views.
I do know that I do not want to write several hundred triggers to enforce RI. Table schema below, the CdValue column is the column that is used throughout the hundreds of tables that use this codes table, and their corresponding column is not named the same.
CREATE TABLE dbo.CodesTable (
PartyGrpId INT  NOT NULL
 , CdTyp  VARCHAR ( 8 ) NOT NULL
 , CompId INT  NOT NULL
 , CdValue VARCHAR ( 8 ) NOT NULL
 , CdValueDesc VARCHAR ( 255 ) NULL
 , AltValueDesc VARCHAR ( 100 ) NULL
[Code] ....
I did though run into one forum where a person brought up a great idea. Filtered Foreign Keys, what a novel concept, if it could work it would make so much less code to fix an issue like this.
ALTER TABLE dbo.BusinessStatus WITH NOCHECK
ADD CONSTRAINT FK_dbo_BusinessStatus_CodesTable FOREIGN KEY (LoanStsDtCd) REFERENCES dbo.CodesTable (CdValue) WHERE CdTyp = 'Status'
View 5 Replies
View Related
Mar 7, 2007
I have a SQL Mobile DB that I am having problems with deletes cascading via foreign keys to delete all child records. The DB is on a WIndows CE5 device that is running a C#.net application.
The are three tables in my DB that relate to this issue (Tests, TestRawInfo, and TestRawData). The Tests table is the main table. TestRawInfo is a child table of Tests and has a foreign key defined that references the Tests primary key (the relationship is 1:1 with the Tests table). TestRawData is a child table of TestRawInfo and has a foreign key defined that references the TestRawInfo primary key (The relationship is 1:many with the TestRawInfo table). All foreign keys are defined with a Cascade on delete. When I delete one or more records from the Tests table I expect the delete to cascade so that all child records are also deleted. Not all the data gets deleted from the TestRawData table, this results in orphan records. I only see a failure however the next time I attempt to compact the database.
Interestingly I can reproduce the problem by opening my Mobile DB in SQL 2005 on my Desktop and deleting data from the Tests table. If however, I add additional records to these tables through SQL 2005 before attempting to delete, the delete works as expected.
Do you have any ideas on what is going on here? Has any one else reported a similar issue? My current work around is to delete data directly from child tables and not rely on the foreign keys to cascade the deletes.
View 5 Replies
View Related
Oct 19, 2005
I'm trying to create relational database with some triggers in SQL Server 7.0, but it doesn't work as expected. Let's say that I have 'Office' database with two tables, 'Users' and 'UserRights' (userRights table should have much more rights, but that's not relevant for this problem):
CREATE TABLE [Users] (
[FS_Username] [nvarchar] (8) NOT NULL ,
[FS_Password] [nvarchar] (32) NOT NULL ,
CONSTRAINT [PK_Users] PRIMARY KEY NONCLUSTERED
(
[FS_Username]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [UserRights] (
[FS_Username] [nvarchar] (8) NOT NULL ,
[FI_UserType] [int] NOT NULL CONSTRAINT [DF_UserRights_FI_UserType] DEFAULT (1),
[FI_AllowLogin] [int] NOT NULL CONSTRAINT [DF_UserRights_FI_AllowLogin] DEFAULT (1),
CONSTRAINT [PK_UserRights] PRIMARY KEY NONCLUSTERED
(
[FS_Username]
) ON [PRIMARY] ,
CONSTRAINT [FK_UserRights_Users] FOREIGN KEY
(
[FS_Username]
) REFERENCES [Users] (
[FS_Username]
)
) ON [PRIMARY]
GO
Foreign Key CONSTRAIN above is created by adding both tables to the diagram and defining relationship between these two tables FS_Username field, where 'Enable relationship for INSERT and UPDATE' option is turned ON. You can easily see this if you create diagram youself and insert these two tables in it.
Next to this, I created two triggers that should handle inserting/deleting rows in UserRights table as consequence of inserting/deleting rows in Users table:
CREATE TRIGGER InsertUserRights ON Users
FOR INSERT
AS
BEGIN
INSERT INTO UserRights (FS_Username) (SELECT FS_Username FROM Inserted)
END
CREATE TRIGGER DeleteUserRights ON Users
FOR DELETE
AS
BEGIN
DELETE UserRights WHERE FS_Username IN
(SELECT FS_Username FROM Users)
END
Now, when (manually) I insert row in Users table, UserRights table gets updated accordingly. HOWEVER, when I try to delete one or more entries from Users table, I get error report. For example, if you try to execute following two commands:
Insert Into Users (FS_Username, FS_Password) VALUES ('John', 's')
Delete from Users
... first command will succede, but second one will fail with message:
DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_UserRights_Users'. The conflict occurred in database 'Office', table 'UserRights', column 'FS_Username'.
The statement has been terminated.
Does anyone know how to resolve this problem without loosing constrains and triggers ? (If I turn off 'Enable relationship for INSERT and UPDATE' option for relationship, things will work fine, but than I can make inconsistent data in UserRights table).
tnx a lot,
Dejan
View 1 Replies
View Related
Apr 11, 2006
Hello again,
I'm going through my tables and rewriting them so that I can create relationship-based constraints and create foreign keys among my tables. I didn't have a problem with a few of the tables but I seem to have come across a slightly confusing hiccup.
Here's the query for my Classes table:
Code:
CREATE TABLE Classes
(
class_id
INT
IDENTITY
PRIMARY KEY
NOT NULL,
teacher_id
INT
NOT NULL,
class_title
VARCHAR(50)
NOT NULL,
class_grade
SMALLINT
NOT NULL
DEFAULT 6,
class_tardies
SMALLINT
NOT NULL
DEFAULT 0,
class_absences
SMALLINT
NOT NULL
DEFAULT 0,
CONSTRAINT Teacher_instructs_ClassFKIndex1 FOREIGN KEY (teacher_id)
REFERENCES Users (user_id)
)
This statement runs without problems and I Create the relationship with my Users table just fine, having renamed it to teacher_id. I have a 1:n relationship between users and tables AND an n:m relationship because a user can be a student or a teacher, the difference is one field, user_type, which denotes what type of user a person is. In any case, the relationship that's 1:n from users to classes is that of the teacher instructing the class. The problem exists when I run my query for the intermediary table between the class and the gradebook:
Code:
CREATE TABLE Classes_have_Grades
(
class_id
INT
PRIMARY KEY
NOT NULL,
teacher_id
INT
NOT NULL,
grade_id
INT
NOT NULL,
CONSTRAINT Grades_for_ClassesFKIndex1 FOREIGN KEY (grade_id)
REFERENCES Grades (grade_id),
CONSTRAINT Classes_have_gradesFKIndex2 FOREIGN KEY (class_id, teacher_id)
REFERENCES Classes (class_id, teacher_id)
)
Query Analyzer spits out: Quote: Originally Posted by Query Analyzer There are no primary or candidate keys in the referenced table 'Classes' that match the referencing column list in the foreign key 'Classes_have_gradesFKIndex2'. Now, I know in SQL Server 2000 you can only have one primary key. Does that mean I can have a multi-columned Primary key (which is in fact what I would like) or does that mean that just one field can be a primary key and that a table can have only the one primary key?
In addition, what is a "candidate" key? Will making the other fields "Candidate" keys solve my problem?
Thank you for your assistance.
View 1 Replies
View Related
Jul 16, 2014
what the best practice is for creating indexes on columns that are foreign keys to the primary keys of other tables. For example:
[Schools] [Students]
---------------- -----------------
| SchoolId PK|<-. | StudentId PK|
| SchoolName | '--| SchoolId |
---------------- | StudentName |
-----------------
The foreign key above is as:
ALTER TABLE [Students] WITH CHECK ADD CONSTRAINT [FK_Students_Schools]
FOREIGN KEY([SchoolId]) REFERENCES [Schools] ([SchoolId])
What kind of index would ensure best performance for INSERTs/UPDATEs, so that SQL Server can most efficiently check the FK constraints? Would it be simply:
CREATE INDEX IX_Students_SchlId ON Students (SchoolId)
Or
CREATE INDEX IX_Students_SchlId ON Students (SchoolId, StudentId)
In other words, what's best practice for adding an index which best supports a Foreign Key constraint?
View 4 Replies
View Related
Nov 22, 2007
Hello!I have a table A with fields id,startdate and other fields. id and startdateare in the primary key.In the table B I want to introduce a Foreign key to field id of table A.Is this possible? If yes, which kind of key I have to build in table A?Thx in advance,Fritz
View 6 Replies
View Related
Dec 13, 2007
I'm using SqlDatasource when the update process is on a primary key, how do you cascade the changes to other tables?
Also when deleting does the cascade deletes the row from another table or only sets the primary key to NULL?
View 1 Replies
View Related
Jul 20, 2005
Hi!For the sake of simplicity, I have three tables, Employee, Department andWorkEmployee >---- Department / /^ ^WorkThe Work table have two columns, empno and depno and consists that theemployee has worked on another department.Here is my scripts:create table employee (empno int not null primary key, depno int not null)create table department (depno int not null primary key)create table work (empno int not null, depno int not null)alter table employee add constraint fk_employee_department foreign key(depno)references department (depno)on update cascadealter table work add constraint fk_work_employee foreign key (empno)references employee (empno)on update cascadealter table work add constraint fk_work_department foreign key (depno)references department (depno)on update cascadeMy problem is the last command. SQL Server responds:Server: Msg 1785, Level 16, State 1, Line 1Introducing FOREIGN KEY constraint 'fk_work_department' on table 'work' maycause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ONUPDATE NO ACTION, or modify other FOREIGN KEY constraints.But I want the depno in the work table to be updated when a department.depnochanges a value.Does anyone have a suggestion on how to overcome this problem?Thanks in advanceBest regards,Gunnar VøyenliEDB-konsulent asNORWAY
View 2 Replies
View Related
Feb 14, 2007
I have a Sql Server 2005 table with 3 fields, an ID field (primary key), a parent ID field, and Name. The parent ID references the ID field (foreign to primary - many to one) within the same table so that records can reference their parent. I would like to place a cascade delete on the ID field so that when the primary ID is removed it will automatically remove all those records with a parent ID that match. Sql server does not allow me to establish this cascade delete.I was considering a trigger instead but only know how tio use the AFTER paramter and not an alternative. Thanks
View 2 Replies
View Related
Nov 13, 2007
I am a C# programmer also acting as the dba for a SQL Server project. I have a table called Folder that has child records in a table called FolderItems. When a Folder is deleted I want the FolderItems to be deleted. I know how to accomplish via code, but I would prefer to have SQL Server perform the delete just in case a folder is deleted via SQL Management Studio or other method.
Is a trigger the way to go about this? I know how to create triggers For Delete, but I just wondering is this the best method to create a Cascade delete or does SQL have any built in "Cascade" delete features.
I am using SQL Server 2005 if this matters.
Thank you,
Corby Nichols
Flying Elephant Software
View 7 Replies
View Related
Mar 7, 2008
Helle people. The question is:
I have two tables: Directories and SubDirectories. SubDirectories table has foreign key for DirectoryId. Now if I define this key with ON DELETE CASCADE attribute then in case of I delete a Directory record, all SubDirectory records will also be deleted. But if I have only one table Directories that have field named ParentId and in this field I save an Id of parent direcory. Can I define a parentId field as a foreign key to the same table and define it with ON DELETE CASCADE? Will it work too? Thanks
View 1 Replies
View Related
Mar 12, 2008
Hi,
I want to know more about delete cascade feature of SQL Server 2005.
View 6 Replies
View Related
Feb 27, 2007
I have two tables 1) tblCustomer (ID, Name, City) 2) tblemp (ID, NAME, Dept.ID, tblcustomer.ID)
Both the tables have ID as PKA emp can be either assigned a) All customers b)single customer c) NO customer
Pls note:- there will never be 2 or 3 customer linked to emp (my actuall requirement tables are different but to explain i am using the above tables)
I know how to assign single customer......but had problem how to link all customers and "no customer"
Please tell me if the following solution is right?1) I will manually insert a record in tblCustomer with id 0 as " all customers" and will not allow the user to delete it 2) store null in FK if it is "no customer"
Also please tell me is it ok to store one more value in tblCustomer as -1 and take it as "No customer"
I have a DDL in the Employee page which should be displaying the names of allthe customers with 2 extra values "ALL CUSTOMERS" and "SELECT" Select is the default value in DDL which says "NO CUSTOMER" selected yet
Thank youSara
View 10 Replies
View Related
Jan 25, 2008
Hi, I have the following tables:
Categories {Category_ID, Column2, ...}
Articles { Article_ID, Category_FK, Column3, ...}
Discussions {Discussion_ID, Article_FK, Column3, ...}
Now, all what I have is just category_ID value (Let us say 3), how can I do cascade delete to delete category's record that its ID = 3 and delete all articles and all discussions that found in that category?
View 2 Replies
View Related
Sep 3, 2002
How to delete a record from a parent table, all relating records in the child tables should also delete.
How to do it by MSSQL 7.0
ANB
View 1 Replies
View Related
Jul 23, 1999
Is it possible to cascade update and delete while DRI remains there ? That
is without deleteing refrential integrity. If anyone have example of cascade delete and Update using Pubs or Northwind Example , i'll be really obliged.
View 1 Replies
View Related
Jun 30, 2004
Hi ,
I have 4 GB db with 6 tables,
table A parent of table B with cascade
update,delete
table B parent of C,D,E,F with cascade
update,delete
Today I deleted range of values(7000 rows) from table A, but NOT ALL data
deleted from B,C,D,E,F
Is any limitation for cascade delete
Db set to simple recovery, no errors deleting data
View 1 Replies
View Related
Aug 4, 1999
Hi everyone,
I would to make a cascade deletion. I dont know how to do it in SQLServer.
Thanks very much.
View 2 Replies
View Related
May 3, 2006
I am attempting to clarify how the cascade delete works. For example I have a Parent table and two child tables used for lookup that have the ID_FK = ID_PK from the Parent table.
tblUsers
---------------
ID_PK
Fname
Lname
...
tblUser_Phone
----------------
ID_FK
Phone
...
tbUser_Email
----------------
ID_FK
Email
...
If I needed to delete all records from tblUser_Phone would this delete my USER entry from the parent table tblUsers if I have cascade delete enabled?
Thanks in advance.
View 2 Replies
View Related
Nov 2, 2006
Hi all, I've been reading for a couple of weeks but this is my first question so please be gentle with me.
I'm pretty new to SQL Server, though I've worked with Access for years. I've got four tables (I'm simplifying) and I need to set up cascade deletes between them. I understand how to create foreign keys and all that, but SQL Server is telling me it can't create my keys because it will create multiple cascade paths. I understand that too, I just need to find a way around it. Here are the tables...
dbo.JOBS
Job_Number
dbo.COLUMNS
JOBS_Job_Number
Column_Number
dbo.ROWS
JOBS_Job_Number
Row_Number
dbo.GRID_DATA
JOBS_Job_Number
Column_Number
Row_Number
Data
Currently I have keys set to cascade delete between JOBS>COLUMNS and JOBS>ROWS so that when the user deletes a job, the columns and rows for that job are deleted as well. No problem there.
If the user deletes a column, I have to delete all the grid data for that column and job. Same with rows. So I tried to establish cascade deletes between COLUMNS>GRID_DATA and ROWS>GRID_DATA and that's where I got in trouble. I assume the reason is that if the user deletes a job, it's going to delete the columns and rows, and I've got multiple cascade paths coming from COLUMNS and ROWS to GRID_DATA. I get it.
The question is, what can I do about it? I don't have a cascade delete between JOBS>GRID_DATA, although I do want that behavior, but I was assuming it just would flow through COLUMNS or ROWS. I haven't bothered to add that key as I'm guessing that would just make the situation worse. Anyone got any advice for me on this one?
Thanks!
Ron Moses
ConEst Software Systems
View 12 Replies
View Related
Jul 23, 2005
Hi. I am trying to log (Not SQL Transaction log) all deleted rowsinto a custome log file after performing "cascade delete".I couldn't find any reference for this in SQL manual or News group.Byung Choi
View 3 Replies
View Related
Sep 6, 2007
Hi,
I have a Configuration Table tblConfig with 45 fields-
PK_Config_Id
ConfigCreatedOn
ConfigEditedOn
ConfigStartDate
ConfigEndDate
ConfigFor
€¦.
€¦.
€¦.
And an Employee table tblEmployee
PK_Emp_Id
EmpName
€¦.
€¦.
€¦.
I would create a Configuration and then apply this config to some Employees €”
So, I created a table tblEmployee_Config with 2 fields
FK_Config_Id
FK_Emp_Id
But I MAY need to change(Edit) few of the properties(fields) of the Configuration record associated to an emp. Therefore I need all the fields of tblConfig table along with employee key in tblEmployee_Config
So, I modified tblEmployee_Config to €”
FK_Emp_Id
FK_Config_Id
ConfigCreatedOn
ConfigEditedOn
ConfigStartDate
ConfigEndDate
ConfigFor
€¦.
€¦.
€¦.
i.e. putting all the fields of the tblConfig into tblEmployee_Config along with emp key
But instead of having 2 almost similar tables (tblConfig and tblEmployee_Config), I merged both the tables into 1 table having Configurations as well as Employees associated to Configs as€”
PK_Config_Id
FK_Emp_Id
FK_Config_Id
ConfigCreatedOn
ConfigEditedOn
ConfigStartDate
ConfigEndDate
ConfigFor
€¦.
€¦.
€¦. Where FK_Emp_Id and FK_Config_Id would be NULL for Configurations.
Like----
PK_Config_Id FK_Emp_Id FK_Config_Id AllowBrks WorkHrs
1 NULL NULL Yes 9
2 100 1 Yes 9
3 101 1 No 8
Here record 1 is a Configuration and records 2 and 3 are Emps records that are associated to Config 1.
Now my question is €“
1. Is it correct if I put a relation like
Primary key Foreign Key
tblEmployee_Config tblEmployee_Config
PK_Config_Id FK_Config_Id
2. Can I perform cascade delete on the same table tblEmployee_Config such that€“
When record 1 is deleted, records 2 and 3 should also get deleted because they refer to the first record?
View 1 Replies
View Related
Nov 4, 2006
Hi,
I Use SQL Server 2005 Express edition. I have a few tables, which are inter-related. For e.g. I have a project table (ProjectID, Name, ...)
I also have a Project invoice table, in which ProjectID is referred as Foreign key.
Now, I want all the rows in Project invoice (child) table table to be deleted (for a particular ProjetID), if I delete the coresponding ProjectID in Projects (parent) table. If I use the 'on delete set null' or 'on delete cascade' constraint, only the ProjectID (in child table) is nullified whereas rest of the columns have data.
Is there any way out, where the whole ROW (not just that field) is deleted? Or, is it that this can be achieved only by some stored procedure / external program?
Thanks!
View 1 Replies
View Related
Jan 15, 2007
Hi, I have a database which saves data about bus links. I want to provide a information to passenger about price of their journay. The price depends on three factors: starting busstop, ending busstop and type of ticket (full, part - for students and old people, ...).
So I created a table with three foreign key constraints (two for busstops and one for type).
When the busstop is deleted or type of ticket I want all data connected with it to be deleted automatically. I wanted to use cascade deleting.
But I receive a following exception: Introducing FOREIGN KEY constraint 'FK_TicketPrices_BusStops1' on table 'TicketPrices' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
How can I achieve my task? Why should it cause cycles or multiple cascade paths?
View 1 Replies
View Related
Sep 20, 2007
I'm using Management Studio Express, is there a way to find out what relationships of a database have cascade delete set to true?
Also is there a way to change the settings (properties) of a relattionship without having to delete it and add it back?
View 1 Replies
View Related
Apr 12, 2006
I am developing an application with ASP .NET 2.0 using SQL Server 2005. I have a very detailed set of relational tables I would like to use to store my data, however I would like to use the built in Membership functionality of .NET 2.0.What I've done is created MembershipProvider that on user creation links the aspnet_Users table to my custome User table.UserFK guid aspNetId int userIdI then use my own userId to link into other data which uses their PKs to link to other tables, etc, etc like any good relational DB should.My question is when I delete a user using the ASP .NET interface I get a contraint error because my User table references the aspnet_Userts.UserId. I initially wanted to toss Cascade On Delete on my tables which would take care of my problem. I was shocked to find out Sql Server 2005 doesnt support Cascades.. and its suggested that Triggers be used instead (ill durned if im gonna make triggers for all my tables)My question is, what is the best practice in deleting from tabled linked with PK/FK relationships? There MUST be a good way to handle this.thanks
View 5 Replies
View Related
Nov 2, 2006
I've been handed a database with over 100 tables and told to find everywhere a cascade delete constraint exists. I could just go through every table by hand and check, but I think there must be an easier way, perhaps an sql query on the master db. Any thoughts?
View 4 Replies
View Related
Mar 24, 2007
for MS SQL 2000
I am trying to do a hierarchical table and i want to add a ON DELETE CASCADE
CREATE TABLE [dbo].[Users](
[id_Users] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
[id_UsersSup] [int] NULL,
[Users] [nvarchar] (100) NOT NULL
) ON [PRIMARY]
ALTER TABLE [dbo].[Users] ADD
CONSTRAINT [FK_Users_Sup] FOREIGN KEY
(
[id_UsersSup]
) REFERENCES [Users] (
[id_Users]
)
ON DELETE CASCADE
but MS SQL refuse to create the foreign key
even if there is 4 levels under the deleted id_Users I want to delete all the rows on all levels under
thank you for helping
View 2 Replies
View Related
Jul 23, 2005
I cannot execute my sql to create a table with ON DELETE CASCADE option.Here is my sql:CREATE TABLE Employees (Name Text(10) not null, Age number,CONSTRAINT pkEmployeesPRIMARY KEY (Name)); <--- This is ok!CREATE TABLE CanTake (Name Text(10) not null, Make Text(10) not null,CONSTRAINT pkCanTakePRIMARY KEY (Name, Make),CONSTRAINT fkCanTakeFOREIGN KEY (Name) REFERENCES EmployeesON UPDATE CASCADE <--- MS-ACCESS says 'syntax error'ON DELETE CASCADE);When I didn't include ON UPDATE CASCADE ON DELETE CASCADE in sql, it works(but that's not what I want). Why? Could anyone suggest me?Thank you.--Message posted via http://www.sqlmonster.com
View 2 Replies
View Related
May 8, 2008
I have 4 colums in a table
Project, Sections,Tasks,Subtasks
Each subtask will haven a row.
I need to write a trigger when I delete a task it needs to delete all the subtasks relating to it. When I delete a section it needs to delete all the tasks and subasks relating to it. similarly for project.
This trigger for task-subtask works.
CREATE TRIGGER "[Deletetasktrigger]" ON [Tbl] FOR DELETE AS
SET NOCOUNT ON
/* * CASCADE DELETES TO '[Tbl B]' */
DELETE [tbl] FROM deleted, [Tbl] WHERE deleted.[task] = [Tbl].[task]
THis works fine. But when I do it for sections I get this error.
"Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)" ..
Help Please!!!!!!
View 1 Replies
View Related
Jan 15, 2008
I want to use "on delete cascade" in one of my tables but I'm worried though whether this can affect the performance when having millions of records. To explain more I'm working on a social networking website and I have two tables UserAccounts, in which I only keep the username and password and a few related fields, and Profiles in which I keep the profile data for users, I want to be sure that I won't have any records in the Profiles table without corresponding records in the UserAccounts table. Please see the DDL below to understand more the structure of the tables:
CREATE TABLE UserAccounts
(
UserID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
UserName VARCHAR(20) NOT NULL,
Password VARCHAR(20) NOT NULL,
--other fields (e.g. last login .. etc)
)
CREATE TABLE Profiles
(
UserID INT NOT NULL REFERENCES UserAccount(UserID),
-- other fields (e.g. birthdate, nationality .. etc)
)
Any suggestions are highly appreciated...
View 6 Replies
View Related
