Hot answers tagged

12

Perhaps simply do it manually with two deletes, in a transaction to ensure consistency: BEGIN TRANSACTION DELETE T2 WHERE employeeID IN (SELECT employeeID FROM T1 WHERE <clauses_to_find_the_records_to_delete> DELETE T1 WHERE <clauses_to_find_the_records_to_delete> COMMIT TRANSACTION You might want to add that FK constraint anyway, with or ...


7

If you want OrderInfo.OrderID to be a foreign key that references OrderNumber.OrderID, then you have to declare it just as INTEGER not as an INTEGER IDENTITY. If it were an identity, you couldn't assign to it the actual value of the corresponding OrderID that you would have retrieved after inserting to OrderNumber. That is, you should declare your tables ...


7

It looks like your model is a one-to-one relationship between OrderNumber and OrderInfo, with OrderInfo.OrderID as the OrderInfo primary key and as a foreign key referencing OrderNumber.OrderID. The error messages suggests you have defined OrderInfo.OrderID as an IDENTITY column. It should not be IDENTITY - the assigned OrderID value from thre related ...


7

The error indicates that you have invalid references in the parentId column. This error shows when you are trying to insert a row with an invalid reference, update an existing row with an invalid reference, or delete a row that is referenced. You also get it when you are trying to create a foreign key constraint on a column that is already populated and ...


5

You can create a trigger to update the UserID columns in related tables, such as: USE Tempdb; CREATE TABLE dbo.Users ( UserID INT NOT NULL CONSTRAINT PK_Users PRIMARY KEY CLUSTERED , ApplicationID INT NOT NULL , Country VARCHAR(255) NOT NULL , DefaultTenantID INT NOT NULL ); GO CREATE TABLE dbo.Tasks ( TaskID INT NOT NULL , ...


5

This question "what can go wrong" can only be answered by the developers at Microsoft or Sybase. When there are multiple cascade paths to the same record, it's possible that the code might attempt to delete it multiple times. If it isn't constructed to deal with the possibility that the record to be deleted has already been deleted, it might throw an error. ...


5

You can't use on delete set null if any of the foreign key columns don't accept null: create schema stack; set search_path=stack; -- create table t1( foo integer , bar integer , primary key(foo,bar) ); -- create table t2( foo integer , baz integer , bar integer , primary key (foo,baz)...


5

It appears as if this is not permitted in innodb which I assume is the engine you are using. From http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html: If ON UPDATE CASCADE or ON UPDATE SET NULL recurses to update the same table it has previously updated during the cascade, it acts like RESTRICT. This means that you cannot use self-...


5

What does it mean to truncate a table at the same time? It means with the same statement. You can truncate more than one tables: TRUNCATE xxxxx, yyyyy RESTART IDENTITY ; More details in Postgres docs: TRUNCATE.


5

I think I have captured what you need in this basic design: db<>fiddle ElementBase Self-fk for the hierarchy: CREATE TABLE dbo.ElementBase ( id integer NOT NULL, parent_id integer NOT NULL, element_type char(1) NOT NULL, -- id key CONSTRAINT [PK dbo.ElementBase id] PRIMARY KEY CLUSTERED (id), -- fk target ...


4

Before worrying about how to get passed this error, you should consider that perhaps this isn't a technical problem, but instead a logical problem. Looking at the data model, I question why you are splitting OrderInfo and OrderNumber in the first place. If they truly are 1-to-1, then what is gained by splitting them? Is it that sometimes you won't have Cost ...


4

With DEFERRABLE foreign key constraints: create or replace procedure cascade_update_primary ( p_table_name in varchar2, p_old_value in number, p_new_value in number ) as l_column_name varchar2(128 char); begin select cc.column_name into l_column_name from user_cons_columns cc join user_constraints c on (c.constraint_name = cc....


4

Some clarifications: REFERENCES is the key word used for a FOREIGN KEY constraint (which allows to cascade DELETE or UPDATE). Your database design seems to have logical flaws. rating seems like a detail of the main table restaurant. Since you have a 1:1 relationship, you could just include the "rating" columns in the main table. If you need a separate ...


4

From Error message 1785 occurs when you create a FOREIGN KEY constraint that may cause multiple cascade paths You receive this error message because in SQL Server, a table cannot appear more than one time in a list of all the cascading referential actions that are started by either a DELETE or an UPDATE statement. For example, the tree of cascading ...


4

run it in a transaction, you'll get warnings for every cascading drop, ROLLBACK if don't like what you see.


3

This is how it's documented: CASCADE will remove a dependent view entirely, but in the foreign-key case it will only remove the foreign-key constraint, not the other table entirely. Emphasis mine


3

You will be unable to delete anything from the referencing table as whatever happens to one part of the key happens to the others (ie: SET NULL, SET DEFAULT, etc) and that will trip the constraint if one needs to be set NULL and the other left as-is. I had the same problem and the work-around is to have a BEFORE DELETE trigger on the referenced table. In ...


3

If this error message did not occur, and ON DELETE SET NULL were to set a column to null, any child-rows referencing the old value would no longer have a valid parent row. Cascading the update to null for all child rows would be required to meet relational integrity requirements. However, cascading might result in a never-ending loop, which is the bit that ...


3

You're close. AFTER triggers happen after foreign key constraint checking. So you need an INSTEAD OF trigger. That way you can modify the child tables before performing the DELETE on the target table. eg -- TABLE t_parent CREATE TABLE t_parent ( m_id INT IDENTITY PRIMARY KEY NOT NULL, m_name nvarchar(450) ); -- TABLE t_child CREATE TABLE t_child ...


2

For that many-to-many 'relation' table, don't bother to have an id. Simply have PRIMARY KEY(case_id, tag_id), INDEX(tag_id, case_id) Note that one of those works perfectly for one of your SELECTs, the other works perfectly for the other. (Use ENGINE=InnoDB.) To get (tag-1, tag-2, tag-3), use GROUP_CONCAT(). FOREIGN KEYs are optional; I would not bother....


2

Aftee searching the Ideone FAQ, it seems that it uses Sqlite3 for the SQL language. Sqlite needs PRAGMA foreign_keys = ON; to enable foreign keys checks. As they say in their docs: SQLite Foreign Key Support : Assuming the library is compiled with foreign key constraints enabled, it must still be enabled by the application at runtime, using the PRAGMA ...


2

You declared the FK constraints "inline" and you fell into a mysql trap. You can check with SHOW CREATE TABLE followers that there is no FK now! This known behaviour is mentioned in the mysql documentation: MySQL does not recognize or support “inline REFERENCES specifications” (as defined in the SQL standard) where the references are defined as part of ...


2

According to the documentation it is not possible: https://dev.mysql.com/doc/refman/5.6/en/drop-tablespace.html Important The tablespace to be dropped must not contain any data files; in other words, before you can drop a tablespace, you must first drop each of its data files using ALTER TABLESPACE ... DROP DATAFILE (see Section 13.1.8, “ALTER ...


2

The idea behind DROP CASCADE is to automatically remove the dependent objects. This is documented on the same manual page that the other answer is referring to: CASCADE Automatically drop objects that depend on the table (such as views). (Emphasis mine.) When you are dropping a table that is referenced by another table, the object that immediately ...


2

I had the same issue. Deleting in batches helps, but still tends to lock up my Percona cluster because of the delay in replicating the delete to other nodes. In my case, most of the cascaded deletes did not exist in practice - there could in theory be records in those tables, but in practice there weren't for most of the tables. So what I did was query ...


2

Try adding Id as one of the key columns of ix_HighSchoolCourseGrade_CourseId_includes. That may help with the additional sort needs as well as back the foreign key. If you can change the foreign key, remove the cascading actions and do your deletes from the referencing tables first. Cascading foreign keys use serializable locks behind the scenes, and when ...


2

I'm not aware of such tool, but you can easily create one your self (foreign-keys-in-a-sql-server-database): SELECT C.TABLE_CATALOG [PKTABLE_QUALIFIER], C.TABLE_SCHEMA [PKTABLE_OWNER], C.TABLE_NAME [PKTABLE_NAME], KCU.COLUMN_NAME [PKCOLUMN_NAME], C2.TABLE_CATALOG [FKTABLE_QUALIFIER], C2.TABLE_SCHEMA [FKTABLE_OWNER], ...


2

The information required to generate the queries and the counts is all available in the catalog views, like sys.columns and sys.foreign_key_columns. We need to find all of the child tables, and then count how many rows in each child table meet the same criteria as the parent ID. CREATE PROCEDURE dbo.GeneratedPathedDeletes @ParentTable nvarchar(512), ...


2

if we were to update student relation wouldn't this be rejected This is correct. If there is no ON UPDATE action defined in the foreign key reference, your update of referenced key would coflict with the foreign key constaint so it would be rejected.


1

You can read information about FKs from sys.foreign_keys. The object_id and referenced_object_id will be references to rows in sys.tables (or sys.objects) so you can pull out the names of the tables involved. If you need more detail add in sys.foreign_key_columns and sys.columns. A simple query on those might be enough to tell you what you need, or you ...


Only top voted, non community-wiki answers of a minimum length are eligible