If I add a foreign key constraint with the following command:  ALTER TABLE [abc]  WITH CHECK ADD  CONSTRAINT [fk_some_col]    FOREIGN KEY([some_col_id]) REFERENCES [xyz] ([some_col_id])   Is there any reason or advantage to follow it with this line?  ALTER TABLE [abc] CHECK CONSTRAINT [fk_some_col]
122382
ALTER TABLE [dbo].[REMINDER_EMAIL]  WITH CHECK ADD  CONSTRAINT    [FK__REMINDER_EMAIL__FACILITY_ID] FOREIGN KEY ( [FACILITY_ID] )   REFERENCES [dbo].[FACILITY] ( [FACILITY_ID] )  ALTER TABLE [dbo].[REMINDER_EMAIL] CHECK CONSTRAINT [FK__REMINDER_EMAIL__FACILITY_ID]
176
I want to check whether a certain criteria is met or not using a trigger before inserting a record. if it meets the criteria, i insert the record, but if it does not i want to display an error message that it doesn't meet the criteria. how do i do it? Please help if anyone knows.
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.
3565
After reading a couple of answers and comments on some SQL questions here, and also hearing that a friend of mine works at a place which has a policy which bans them, I'm wondering if there's anything wrong with using backticks around field names in MySQL.  That is:  SELECT `id`, `name`, `anotherfield` ... -- vs -- SELECT id, name, anotherfield ...
108763
I want to add a check constraint on a column if entered value for column is greater than 3 than it should be saved as 1 else entered value. For that scenario I am using below query but on insertion of data it shows an error message.  create table tblTestCheckConstraint  ( id int, NewColumn int )   alter table tblTestCheckConstraint add constraint chk_tblTestCheckConstraint_NewColumn1 CHECK   (     CASE         WHEN NewColumn &gt;4 THEN 1         ELSE NewColumn     END = 1 )   Insert Statement:   insert into tblTestCheckConstraint values ( 1,5) insert into tblTestCheckConstraint values ( 1,2)   Error Message:   Msg 547, Level 16, State 0, Line 1 The INSERT statement conflicted with the CHECK constraint "chk_tblTestCheckConstraint_NewColumn1". The conflict occurred in database "DBName" , table "dbo.tblTestCheckConstraint", column 'NewColumn'. The statement has been terminated.
46162
I need to have one column as the primary key and another to auto increment an order number field. Is this possible?  EDIT: I think I'll just use a composite number as the order number. Thanks anyways.
60176
I have a financial transaction that looks something like this (pseudo):  begin     select record for update     update record(same record as selected)     insert another record into another table commit   If I call SELECT ... FOR UPDATE within the transaction, will the lock be released for the specific rows only when the transaction is committed ?
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.
50341
I have a database of components. Each component is of a specific type. That means there is a many-to-one relationship between a component and a type. When I delete a type, I would like to delete all the components which has a foreign key of that type. But if I'm not mistaken, cascade delete will delete the type when the component is deleted. Is there any way to do what I described?
24352
I have the following design. Each user has it's own contacts, it's own hosts and assign those to a domain which belongs only to him.   I want to insure that the host you assign to the domain belongs to the user that domain is assigned to.  Is it something that I have to insure in my application logic?  If I set domains.user_id foreign key to reference host.user_id and user_id.id does it eliminate my issue?    By the way can anyone suggest me a better design for my schema?
6092
How to list all constraints (Primary key, check, unique mutual exclusive, ..) of a table in PostgreSQL?
55667
I've created a table in MySQL:  CREATE TABLE actions ( A_id int NOT NULL AUTO_INCREMENT, type ENUM('rate','report','submit','edit','delete') NOT NULL, Q_id int NOT NULL, U_id int NOT NULL, date DATE NOT NULL, time TIME NOT NULL, rate tinyint(1), PRIMARY KEY (A_id), CONSTRAINT fk_Question FOREIGN KEY (Q_id) REFERENCES questions(P_id), CONSTRAINT fk_User FOREIGN KEY (U_id) REFERENCES users(P_id));   This created the table I wanted just fine (although a "DESCRIBE actions;" command showed me that the foreign keys were keys of type MUL, and I'm not sure what this means).  However, when I try to enter a Q_id or a U_id that does not exist in the questions or users tables, MySQL still allows these values.  What did I do wrong?  How can I prevent a table with a foreign key from accepting invalid data?  UPDATE 1  If I add TYPE=InnoDB to the end, I get an error:     ERROR 1005 (HY000): Can't create table './quotes/actions.frm' (errno: 150)   Why might that happen?  UPDATE 2  I'm told that it's important to enforce data integrity with functional foreign keys, but also that InnoDB should not be used with MySQL.  What do you recommend?
91339
I am running the below query in ideone   CREATE TABLE AUTHOR ( AUTHOR_ID NUMBER(5) PRIMARY KEY, AUTHOR_NAME VARCHAR2(18) );  INSERT INTO AUTHOR (AUTHOR_ID, AUTHOR_NAME) VALUES (1234, 'MC GRAW HILL'); INSERT INTO AUTHOR (AUTHOR_ID, AUTHOR_NAME) VALUES (1235, 'J K ROLLING');     INSERT INTO AUTHOR (AUTHOR_ID, AUTHOR_NAME) VALUES (1236, 'WILLAM'); INSERT INTO AUTHOR (AUTHOR_ID, AUTHOR_NAME) VALUES (1237, 'SHAKESPEAR');  --------------------------------- CREATE TABLE BOOK  ( BOOK_ID NUMBER(5) PRIMARY KEY, BOOK_NAME VARCHAR2(15), AUTHOR_ID NUMBER(5), CONSTRAINT author_fk FOREIGN KEY (AUTHOR_ID) REFERENCES AUTHOR(AUTHOR_ID) ON DELETE CASCADE );  INSERT INTO BOOK (BOOK_ID, BOOK_NAME, AUTHOR_ID)      VALUES (10001, 'SOFTWARE DEVELOPEMENT', 1234); INSERT INTO BOOK (BOOK_ID, BOOK_NAME, AUTHOR_ID)      VALUES (10002, 'HARRY POTTER', 1235); INSERT INTO BOOK (BOOK_ID, BOOK_NAME, AUTHOR_ID)      VALUES (10003, 'TO BE OR NOT TO BE', 1237); INSERT INTO BOOK (BOOK_ID, BOOK_NAME, AUTHOR_ID)      VALUES (10004, 'PROJECT MANAGEMENT', 1234);   After creating these entries, when I try to delete the entries in AUTHOR Table by using following command:  DELETE FROM AUTHOR WHERE AUTHOR_ID = 1234;   Select * from book ;  10001|SOFTWARE DEVELOPEMENT|1234 10002|HARRY POTTER         |1235 10003|TO BE OR NOT TO BE   |1237 10004|PROJECT MANAGEMENT   |1234   and  Select * from author ;  1235|J K ROLLING 1236|WILLAM 1237|SHAKESPEAR   the entry from parent table is deleted but their is no delete cascade effect in child table (BOOK table). Can someone help me what I am doing wrong.  Thanks,
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.
113469
The objective is to have assignment rules fired on a Lead record creation. It seems the rule can be assigned only when using "update" DML in an "after insert" trigger. Why does another DML must be executed after insert to have lead assignment rule assigned?   //Does not work trigger LeadTrigger on Lead (before insert) {         for (Lead l : Trigger.new) {                 Database.DMLOptions dmo = new Database.DMLOptions();         dmo.assignmentRuleHeader.useDefaultRule = true;         so.setOptions(dmo);     } }   //Works: Records must be *updated* in after insert trigger in order to have the rule assigned trigger LeadTrigger on Lead (after insert) {     List&lt;Lead&gt; ls = new List&lt;Lead&gt;();      for (Lead l : Trigger.new) {         ls.add(new Lead(id = l.id));     }      Database.DMLOptions dmo = new Database.DMLOptions();     dmo.assignmentRuleHeader.useDefaultRule = true;     Database.update(ls, dmo); }   Moreover, testing of the rule assignment fails even though the rule has been assigned to the Lead. We cannot test assignment rule outcome (ownerId=defaultQueueId for example) since the rule will change on PROD eventually.  //Test method failing for a lead *having* rule assigned:     public static void assertDefaultAssignmentRules(SObject sobj) {     Database.DMLOptions dmo = sobj.getOptions();      System.assertNotEquals(null, dmo, 'DMLOption is null');     System.assert(dmo.assignmentRuleHeader.useDefaultRule, dmo); }
109033
In mysql, how do I get the primary key used for an insert operation, when it is autoincrementing.  Basically, i want the new autoincremented value to be returned when the statement completes.  Thanks!
21299
I would like to add an order column wich will be position of my record. Positions are connected with name. If the name is S1 - order should be 1, for S2 - order should equal 2 etc. How can I do this?  Real state:   id       name     position -------  -------  ------- 1        S4       null 2        S2       null 3        S3       null 4        S1       null     Expected:   id       name     position -------  -------  ------- 1        S4       4 2        S2       2 3        S3       3 4        S1       1   I tried with update:  WITH x AS (   SELECT       id,       name,       ROW_NUMBER() OVER (ORDER BY name) position   FROM       stage ) UPDATE     stage SET     position = x.position FROM     x WHERE     x.id = stage.id;   Stage is the name of the table.