So I know what the all the child foreign keys are by looking   SELECT *  FROM INFORMATION_SCHEMA.STATISTICS;   However I now need to get what the parents of the foreign keys are called and what tables they are stored in. So I need the SQL to return a table which stores the following data:  | Child Table | Child Column | Parent Table     | Parent Column | ================================================================= | #_article   | category     | #_category_store | id            |   I have tried using this:  SELECT * FROM information_schema.REFERENTIAL_CONSTRAINTS;   However that only gives me the child table and parent table. I cant then find the parent column.
16538
In MySQL, how do I get a list of all foreign key constraints pointing to a particular table? a particular column? This is the same thing as , but for MySQL.
59811
Here is the script to create my tables:   CREATE TABLE clients (    client_i INT(11),    PRIMARY KEY (client_id) ); CREATE TABLE projects (    project_id INT(11) UNSIGNED,    client_id INT(11) UNSIGNED,    PRIMARY KEY (project_id) ); CREATE TABLE posts (    post_id INT(11) UNSIGNED,    project_id INT(11) UNSIGNED,    PRIMARY KEY (post_id) );   In my PHP code, when deleting a client, I want to delete all projects posts:  DELETE  FROM posts INNER JOIN projects ON projects.project_id = posts.project_id WHERE projects.client_id = :client_id;   The posts table does not have a foreign key client_id, only project_id. I want to delete the posts in projects that have the passed client_id.  This is not working right now because no posts are deleted.
81924
I'm trying to find the best way for a basic security system for a website.  I know I want users and groups.  I thought I'd have:  user_table user_id user_name ...  group_type group_id group_name parent_id ...  group_table id user_id group_id   First one is the user, second one is the group, and the third one is the intermediary table that connects the two.  One user has many groups.  Does this sound ok?
54151
I have sysadmin rights on a SQL Server 2008 R2 box. I found a script that can drop all foreign keys and re-add them for us when we need to after a testing import. So I execute the below code with no syntax errors as it displays the msg that says "Alter Table [ParentTable] Drop Constraint FK_Name"... for a total of 23 keys, but yet when I refresh the Server or the DB, and Tables, I still see them when I expand the Keys node for each table? so why its not dropping them? Also, do I need to convert it to a permanent table or put the script in a stored procedure in case if I want to run it again for testing purposes? Please advice.  I used the following script from "Swastik Mishra" posted by "Pinal Dave"        SET NOCOUNT ON DECLARE @table TABLE( RowId INT PRIMARY KEY IDENTITY(1, 1), ForeignKeyConstraintName NVARCHAR(200), ForeignKeyConstraintTableSchema NVARCHAR(200), ForeignKeyConstraintTableName NVARCHAR(200), ForeignKeyConstraintColumnName NVARCHAR(200), PrimaryKeyConstraintName NVARCHAR(200), PrimaryKeyConstraintTableSchema NVARCHAR(200), PrimaryKeyConstraintTableName NVARCHAR(200), PrimaryKeyConstraintColumnName NVARCHAR(200) ) INSERT INTO @table(ForeignKeyConstraintName, ForeignKeyConstraintTableSchema,     ForeignKeyConstraintTableName, ForeignKeyConstraintColumnName) SELECT U.CONSTRAINT_NAME, U.TABLE_SCHEMA, U.TABLE_NAME, U.COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE U INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C ON U.CONSTRAINT_NAME = C.CONSTRAINT_NAME WHERE C.CONSTRAINT_TYPE = 'FOREIGN KEY' UPDATE @table SET PrimaryKeyConstraintName = UNIQUE_CONSTRAINT_NAME FROM @table T INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R ON T.ForeignKeyConstraintName = R.CONSTRAINT_NAME UPDATE @table SET PrimaryKeyConstraintTableSchema = TABLE_SCHEMA, PrimaryKeyConstraintTableName = TABLE_NAME FROM @table T INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C ON T.PrimaryKeyConstraintName = C.CONSTRAINT_NAME UPDATE @table SET PrimaryKeyConstraintColumnName = COLUMN_NAME FROM @table T INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE U ON T.PrimaryKeyConstraintName = U.CONSTRAINT_NAME --SELECT * FROM @table --DROP CONSTRAINT: SELECT ' ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' +      ForeignKeyConstraintTableName + '] DROP CONSTRAINT ' + ForeignKeyConstraintName + '  GO’ FROM @table –ADD CONSTRAINT: SELECT ‘ ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' +   ForeignKeyConstraintTableName + '] ADD CONSTRAINT ‘ + ForeignKeyConstraintName + ‘ FOREIGN KEY(‘ +   ForeignKeyConstraintColumnName + ‘) REFERENCES [' + PrimaryKeyConstraintTableSchema + ']. [' + PrimaryKeyConstraintTableName + '](‘ + PrimaryKeyConstraintColumnName + ‘)  GO’ FROM @table GO
85405
I’m having some trouble in recreating a database with a one-to-many (1:M) relationship between Users and Items. This is pretty straightforward, yes; however, each Item belongs to a certain Category (e.g., a Car, a Boat or a Plane), and each Category has a particular number of attributes, e.g.: Car structure: +----+--------------+--------------+ | PK | Attribute #1 | Attribute #2 | +----+--------------+--------------+  Boat structure: +----+--------------+--------------+--------------+ | PK | Attribute #1 | Attribute #2 | Attribute #3 | +----+--------------+--------------+--------------+  Plane structure: +----+--------------+--------------+--------------+--------------+ | PK | Attribute #1 | Attribute #2 | Attribute #3 | Attribute #4 | +----+--------------+--------------+--------------+--------------+  Because of this diversity in the number of attributes (columns), I initially thought it would be a good idea to create one separate table for each Category, so I would avoid several NULLs and thus making a better use of the indexing. Although it looked great at first, I couldn’t find a way to create the relationship between the Items and the Categories through the database because, at least in my modest experience as a database administrator, when creating Foreign Keys, I inform explicitly a database the table name and column. In the end, I would like a solid structure to store all data, while having all the means to list all attributes of all Items a User may have with one query. I could hardcode dynamic queries with the server-side language, but I feel this is wrong and not very optimal. Additional information These are my responses to MDCCL comments:  1. How many Item Categories of interest are there in your business context, three (i.e., Cars, Boats and Planes) or more?  In fact, it’s very simple: There are only five Categories in total.  2. Will the same Item always belong to the same User (that is, once a given Item has been “assigned” to a certain User it cannot be changed)?  No, they could change. In the fictional scenario of the question, it would be like User A sells Item #1 for User B, so the ownership must be reflected.  3. Are there attributes that are shared by some or all of the Categories?  Not shared but, from memory, I can tell that at least three attributes are present in all Categories.  4. Is there a chance that the cardinality of the relationship between User and Item is many-to-many (M:N) instead of one-to-many (1:M)? For example, in the case of following business rules: A User owns zero-one-or-many Items and An Item is owned by one-to-many Users  No, because Items would describe a physical object. Users will have a virtual copy of them, each one identified by a unique   5. Regarding your following response to one of the question comments:  “In the fictional scenario of the question, it would be like User A sells Item #1 for User B, so the ownership must be reflected.”  It seems that you are planning to keep track of the item ownership evolution, so to speak. In this way, which attributes would you like to store about such phenomenon? Only the modification of the attribute that indicates the specific User who is the Owner of a specific Item?  No, not really. The ownership may change, but I don’t need to keep track of the previous Owner.
88467
We have a question related to a DB query :   CREATE TABLE `Action` (   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,   `emp_Id` int(10) unsigned NOT NULL,   `name` varchar(60) NOT NULL,   `updated_action_at` datetime(3) DEFAULT NULL,   `created_At` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,   `notes` varchar(400) DEFAULT NULL,   PRIMARY KEY (`id`),    KEY `action_empId_fk` (`emp_Id`),    CONSTRAINT `action_empId_fk` FOREIGN KEY (`emp_Id`) REFERENCES `Employee` (`id`) ON DELETE CASCADE,  ) ENGINE=InnoDB AUTO_INCREMENT=502004 DEFAULT CHARSET=latin1   CREATE TABLE `ActionAssignedTo` (   `action_Id` int(10) unsigned DEFAULT NULL,   `emp_Id` int(10) unsigned DEFAULT NULL,   KEY `actionassignedto_emp_id_foreign` (`emp_Id`),   KEY `actionassignedto_action_id_foreign` (`action_Id`),   CONSTRAINT `ActionAssignedTo_ibfk_1` FOREIGN KEY (`emp_Id`) REFERENCES `Employee` (`id`) ON DELETE CASCADE,   CONSTRAINT `ActionAssignedTo_ibfk_2` FOREIGN KEY (`action_Id`) REFERENCES `Action` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1  CREATE TABLE `Employee` (   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,   `vendor_Id` int(10) unsigned DEFAULT NULL,   `name` varchar(40) NOT NULL,   `mobile_Number` varchar(15) NOT NULL,   `active` tinyint(1) DEFAULT '1',   `updated_At` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,   `created_At` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,   PRIMARY KEY (`id`),   KEY `employee_vendor_id_foreign` (`vendor_Id`),   CONSTRAINT `employee_vendor_id_foreign` FOREIGN KEY (`vendor_Id`) REFERENCES `Vendor` (`vendor_Id`) ) ENGINE=InnoDB AUTO_INCREMENT=511 DEFAULT CHARSET=latin1   We want to find the action created by employee with Action.empId = 4 along with the list of Assignee (an action can be assigned to multiple people)  We have the following query :   Select     notes,             Action.id,             AssigneeName.name,             ActionAssignedTo.emp_Id  from       Employee inner join Action  on         (Action.emp_Id = Employee.id  and        Action.emp_Id in (select Employee.id                               from Employee                               where Employee.vendor_Id = 1)) inner      join ActionAssignedTo  on         Action.id =  ActionAssignedTo.action_Id inner join Employee as AssigneeName  on         ActionAssignedTo.emp_Id =  AssigneeName.id where      Action.emp_Id = 4   Now, lets say if Action with id = 10 was assigned to 2 people : John (empId : 2) And Jack (empId : 5) --> the above query returns following results :  (columns from the query)  notes                       | Action.id | AssigneeName.name | ActionAssignedTo.emp_Id 1. Finish Designing Queries | 10        | John              | 2 2. Finish Designing Queries | 10        | Jack              | 5   If we observe the above results the first 2 columns get repeated for the Assignees for SAME ACTION ID  --> is there a way we can merge them something like   1. Finish Designing Queries |10         | { John : 2  , Jack  : 5}   Basically records being merged into one.
91878
I imported a bunch of tables from SQL Server 2000 to my 2008 database. All the imported tables are prefixed with my username eg: erpadmin.tablename.  In the table properties it lists 'erpadmin' as the db schema. When I write a query I now have to include 'erpadmin.' in front of all the table names which is confusing.  Current result:  select * from erpadmin.tablename   Desired result:  select * from  tablename
4453
I don't have much experience with DB design, and having a hard time figuring out what's wrong with the following:   Table father has a composite PK (col1, col2) Table closure has ancestor(a-col1, a-col2), descendant(d-col1, d-col2) and length. Ancestor and Descendant are composite FKs, each pointing to the PK of father. The 4 columns (a-col1, a-col2,d-col1, d-col2) are a composite PK.   Look at the following fiddle:   I am working with MySQL.  I have constraints in CASCADE, but when I try to update the father table, the constraints give me errors. I can't do the following update to father table row:  UPDATE father  SET `col1` = "UPDATE_MY_ID_PLEASE"  WHERE `col1` = 'A' AND `col2` = 1;   Is the design wrong? How should this be done?
48109
I am trying to get the Ultimate Parent Account from my contact up to 3rd level parent. Here is my SOQL code:  SELECT Name,Account.Parent.Name, Account.Parent.Parent.Name, Account.Parent.Parent.Parent.Name FROM Contact   I expect to see this result:  Contact:{Name='Name of COntact',Parent='the parent name', ChildParent='Child Parent Name', GrandParent='Grand Parent Name'}    OR   Contact:{Name='Name of COntact',ParentId='the parent Id', ChildParentId='Child Parent Id', GrandParentId='Grand Parent Id'}   Once I debug my query the result is not what i expected.  Contact:{Name='Name of the Contact', AccountId='Parent Id', Id='Id of the Contact'}   Anyone help?
85665
Okay, how would I do this?  class Example(models.Model):   parent_example = models.ForeignKey(Example)   I want to have a model have a foreign key reference to itself.  When I try to create this I get a django validation error that Example is not yet defined.
54587
A Google search spewed forth millions of hits on how to find tables without clustered indexed, the PK normally being the clustered index of a table. However, a table could easily have a natural key as a clustered index, and a non-clustered surrogate index, like an identity column.  How do I find all tables in a DB without a primary key defined? I have 245 tables in this DB: manual inspection is grossly inefficient.
114845
Consider in my basket, I have a package (Main Product-just a holder) which will have 2 standalone products (child) and again the standalone products will have child products (Grand Parent). All these products are from same object called Product Configuration.   We have a child relationship to itself(Parent Configuration) using a custom lookup field. The problem here is, I could only fetch details only up to 1 level (i.e.,package and child details) and I was not able to drill deeper to grand child products.  Query I used to get 1 level of relation ships is below:  SELECT id, name, Access_Type__c, Circuit_Bandwidth_Value__c, QuoteType__c,     Upstream_Speed_Value__c, IP_Address_Type__c, Care_Level__c, Base_Plan_Value__c, Site_Address__c,      cscfga__Quantity__c, Quantity_of_Ip_Address__c, cscfga__Contract_Term__c, Connection_Price__c,      Monthly_Rental_Conga__c, Total_Annual_Rental__c,      (         SELECT id, name,cscfga__Product_Family__c,Upstream_Speed_Value__c, cscfga__Quantity__c, Quantity_of_Ip_Address__c,              Connection_Price__c, Monthly_Rental_Conga__c,cscfga__Parent_Configuration__r.Circuit_Bandwidth_Value__c,Circuit_Bandwidth_Value__c          FROM cscfga__Related_Configurations__r         WHERE cscfga__Parent_Configuration__c &lt;&gt; ''          ORDER BY name     )  FROM cscfga__Product_Configuration__c  WHERE cscfga__Product_Basket__r.cscfga__Opportunity__c='{pv0}'      AND cscfga__Product_Basket__r.csordtelcoa__Synchronised_with_Opportunity__c=True     AND cscfga__Parent_Configuration__c = ''  ORDER BY name   Not sure how to write another sub query to get grand child details. When I tried the below query:  SELECT id, name, Access_Type__c, Circuit_Bandwidth_Value__c, QuoteType__c, Upstream_Speed_Value__c,      IP_Address_Type__c, Care_Level__c, Base_Plan_Value__c, Site_Address__c, cscfga__Quantity__c,      Quantity_of_Ip_Address__c, cscfga__Contract_Term__c, Connection_Price__c,      Monthly_Rental_Conga__c, Total_Annual_Rental__c,     (         SELECT id, name, cscfga__Product_Family__c, Upstream_Speed_Value__c, cscfga__Quantity__c,              Quantity_of_Ip_Address__c, Connection_Price__c, Monthly_Rental_Conga__c, cscfga__Parent_Configuration__r.Circuit_Bandwidth_Value__c, Circuit_Bandwidth_Value__c,         FROM cscfga__Related_Configurations__r         WHERE cscfga__Parent_Configuration__c &lt;&gt; ''          ORDER BY name     ),     (         SELECT id, name, cscfga__Product_Family__c          FROM cscfga__Related_Configurations__r         WHERE cscfga__Parent_Configuration__c &lt;&gt; ''          ORDER BY name     ) FROM cscfga__Product_Configuration__c  WHERE cscfga__Product_Basket__r.cscfga__Opportunity__c='0066E000002Xrr0'      AND cscfga__Product_Basket__r.csordtelcoa__Synchronised_with_Opportunity__c=True      AND cscfga__Parent_Configuration__c = ''  ORDER BY name   I got below error:     MALFORMED_QUERY:  from cscfga__Related_Configurations__r ^ ERROR at   Row:6:Column:6 Cannot follow the same aggregate relationship twice:   cscfga__Related_Configurations__r   Can someone please help here. Note: I am executing these queries in Workbench.
114689
I am practising in writing a simple forum and just can't decide on table structure. Currently I have this:      id  int(11)     AUTO_INCREMENT           title   varchar(56)      group_id    int(11)     group_parent    tinyint(1)     access_from int(11)      alliance_forum  tinyint(1)                alliance_id int(11)     alliance_role_access    int(11)   Group parent is a 0 or 1 column which states wherever a forum is parent of forums, and group id is for group forums whch reffers to same table to record with group parent set to 1. Alliance forum is same as group parent, as the alliance id,and access column are for access control. Which would be the best and perfomant way of restructuring this? Should I mak 3 different tables for alliance forums, forum_groups and group_forums or keep it this way? P.s. Please remove my  another same question.
2806
My schema 'test' has 3 tables: 'user', 'post' and 'revision'.  CREATE TABLE IF NOT EXISTS `test`.`user` (   `iduser` INT UNSIGNED NOT NULL AUTO_INCREMENT,   `name` VARCHAR(45) NOT NULL,    PRIMARY KEY (`iduser`)) ENGINE = InnoDB;  CREATE TABLE IF NOT EXISTS `test`.`post` (   `idpost` INT UNSIGNED NOT NULL AUTO_INCREMENT,   `iduser` INT UNSIGNED NOT NULL,   `content` VARCHAR(4000) NOT NULL,   `revision_count` INT NOT NULL DEFAULT 0,    PRIMARY KEY (`idpost`),    INDEX `fk_post_user1_idx` (`iduser` ASC),    CONSTRAINT `fk_post_user1`      FOREIGN KEY (`iduser`)      REFERENCES `test`.`user` (`iduser`)      ON DELETE NO ACTION      ON UPDATE NO ACTION) ENGINE = InnoDB;  CREATE TABLE IF NOT EXISTS `test`.`revision` (   `idrevision` INT UNSIGNED NOT NULL,   `iduser` INT UNSIGNED NOT NULL,   `idpost` INT UNSIGNED NOT NULL,   `content` VARCHAR(4000) NOT NULL,    UNIQUE INDEX `index1` (`idpost` ASC, `idrevision` ASC),    PRIMARY KEY (`idrevision`, `iduser`),    INDEX `fk_revision_user1_idx` (`iduser` ASC),    CONSTRAINT `fk_revision_post`      FOREIGN KEY (`idpost`)      REFERENCES `test`.`post` (`idpost`)      ON DELETE NO ACTION      ON UPDATE NO ACTION,    CONSTRAINT `fk_revision_user1`      FOREIGN KEY (`iduser`)      REFERENCES `test`.`user` (`iduser`)      ON DELETE NO ACTION      ON UPDATE NO ACTION) ENGINE = InnoDB;   And there is the script creates data:  INSERT INTO `test`.`user` (`iduser`, `name`) VALUES (1, 'Ann'); INSERT INTO `test`.`user` (`iduser`, `name`) VALUES (2, 'Bob');  INSERT INTO `test`.`post` (`idpost`, `iduser`, `content`, `revision_count`) VALUES (1, 1, 'this is first post', 1);  INSERT INTO `test`.`revision` (`idrevision`, `iduser`, `idpost`, `content`) VALUES (1, 1, 1, 'this is old content of first post');   I create 2 sessions work concurrently. Session 1 works with query:  BEGIN; INSERT INTO revision (idrevision, idpost, iduser, content) VALUES     ((SELECT max_idrevision + 1      FROM (SELECT max(idrevision) AS max_idrevision           FROM revision WHERE idpost = 1) AS anon_1),     1, 2, 'abc xyz');   Now session 2 works:   BEGIN; UPDATE revision SET idrevision=3 WHERE idpost=1 AND iduser =1; -- will hang by lock  -- but with this update UPDATE revision SET content='nwe asdfs' WHERE idpost=1 AND iduser=1; -- not hang   I don't know what's lock mysql used with query insert in session 1? Is it row lock or table lock or anything else ? If it's row lock, why does second update query in session 2 not hang ?
35597
I'm setting up a database using phpMyAdmin. I have two tables (foo and bar), indexed on their primary keys. I am trying to create a relational table (foo_bar) between them, using their primary keys as foreign keys. I created these tables as MyISAM, but have since changed all three to InnoDB, because I read that MyISAM doesn't support foreign keys. All id fields are INT(11). When I choose the foo_bar table, click the &quot;relation view&quot; link, and try to set the FK columns to be database.foo.id and database.bar.id, it says &quot;No index defined!&quot; beside each column. What am I missing? Clarification/Update For the sake of simplicity, I want to keep using phpMyAdmin. I am currently using XAMPP, which is easy enough to let me focus on the PHP/CSS/Javascript, and it comes with phpMyAdmin. Also, although I haven't been able to set up explicit foreign keys yet, I do have a relational table and can perform joins like this: SELECT *  FROM foo  INNER JOIN foo_bar  ON foo.id = foo_bar.foo_id  INNER JOIN bar ON foo_bar.bar_id = bar.id;  It just makes me uncomfortable not to have the FKs explicitly defined in the database.
119024
I have a big database that I need to extract all primary keys and foreign keys from each table.  I have pgAdmin III.   Is there a way to do this automatically and not go over each table manually?
15483
I'm experiencing an annoying problem with a table named SysMessages in one of our databases. When I run a select statement I don't get the contents of this table, but of the sys.sysmessages view in the master database. A use [DBNAME] doesn't help, nor select * from [DBNAME].dbo.SysMessages.  I'm in the middle of a migration to a newer SQL Server version. In SQL Server 2008 R2 I didn't encounter this problem with this database, but in SQL Server 2016 I'm not able to query my own SysMessages table.  Any help would be greatly appreciated.