If your cascading deletes nuke a product because it was a member of a category that was killed, then you’ve set up your foreign keys improperly. Given your example tables, you should have the following table setup: CREATE TABLE categories ( id int unsigned not null primary key, name VARCHAR(255) default null )Engine=InnoDB; CREATE TABLE … Read more
You can’t. Although the SQL-92 syntax to add a foreign key to your table would be as follows: ALTER TABLE child ADD CONSTRAINT fk_child_parent FOREIGN KEY (parent_id) REFERENCES parent(id); SQLite doesn’t support the ADD CONSTRAINT variant of the ALTER TABLE command (sqlite.org: SQL Features That SQLite Does Not Implement). Therefore, the only way to add … Read more
The standard convention in SQL Server is: FK_ForeignKeyTable_PrimaryKeyTable So, for example, the key between notes and tasks would be: FK_note_task And the key between tasks and users would be: FK_task_user This gives you an ‘at a glance’ view of which tables are involved in the key, so it makes it easy to see which tables … Read more
Assuming this table: CREATE TABLE students ( student_id SERIAL PRIMARY KEY, player_name TEXT ); There are four different ways to define a foreign key (when dealing with a single column PK) and they all lead to the same foreign key constraint: Inline without mentioning the target column: CREATE TABLE tests ( subject_id SERIAL, subject_name text, … Read more
No, this will not drop your table if there are indeed foreign keys referencing it. To get all foreign key relationships referencing your table, you could use this SQL (if you’re on SQL Server 2005 and up): SELECT * FROM sys.foreign_keys WHERE referenced_object_id = object_id(‘Student’) and if there are any, with this statement here, you … Read more
Because these two lines … EmployeeService es = new EmployeeService(); CityService cs = new CityService(); … don’t take a parameter in the constructor, I guess that you create a context within the classes. When you load the city1… Payroll.Entities.City city1 = cs.SelectCity(…); …you attach the city1 to the context in CityService. Later you add a … Read more
As explained here, seems the foreign key constraint has to be dropped by constraint name and not the index name. The syntax is: ALTER TABLE footable DROP FOREIGN KEY fooconstraint;
No. There is no such option available from Oracle SQL Developer. You have to execute a query by hand or use other tool (For instance PLSQL Developer has such option). The following SQL is that one used by PLSQL Developer: select table_name, constraint_name, status, owner from all_constraints where r_owner = :r_owner and constraint_type=”R” and r_constraint_name … Read more
Do not hesitate to put constraints on the database. You’ll be sure to have a consistent database, and that’s one of the good reasons to use a database. Especially if you have several applications requesting it (or just one application but with a direct mode and a batch mode using different sources). With MySQL you … Read more
It’s because the name column on the bar table does not have the UNIQUE constraint. So imagine you have 2 rows on the bar table that contain the name ‘ams’ and you insert a row on baz with ‘ams’ on bar_fk, which row on bar would it be referring since there are two rows matching?