New answers tagged

4

Will the optimizer recognize the change and consider them before using cached query plans for re-run transactions? Put another way, do I need to consider removing any query plans? Yes, the optimizer should recompile the plans since changes to an underlying table are one of the triggers that result in a plan recompilation. Since checking a constraint ...


0

To quote MSDN as an authoritative source: There are no significant differences between creating a UNIQUE constraint and creating a unique index that is independent of a constraint. Data validation occurs in the same manner, and the query optimizer does not differentiate between a unique index created by a constraint or manually created. However, creating ...


1

Thanks to the Suggestions above I was able to solve it, I combined both solution. (One is I was doing it backwards) The Other that using a Title is a Bad Id. I simply substituted the title for a Game_ID. (Different Game_ID's might share a similar title and they are numbers.) CREATE TABLE Employee ( Employee_ID INT NOT NULL, First_Name CHAR(30) NOT ...


2

This is what you're after: delimiter $ create trigger student_validate before insert on student for each row begin if new.admission not rlike '^[0-9][/][0-9][0-9][0-9][0-9][/][0-9][0-9]$' then signal sqlstate '45000' set message_text = 'Bad Format Admission Number'; end if; end$ delimiter ;


1

Someone named "RhodiumToad" in the #postgresql channel on IRC pointed out the problem. Because I didn't explicitly state the column name, the 2nd table is referencing the primary key column "id" from the first table. Instead, I wanted it to reference the drug_code column. So where I had previously defined the 2nd table as: CREATE TABLE dpd.inactive ( ...


4

It appears you are putting the foreign key on the wrong table. The column PersonID on the Jobs table should reference the PersonID on the Employee table. If you are assigning persons to jobs, then your original foreign key should be on JobId. This would require adding the JobId to the Employees table and removing the PersonId from the Jobs table. If ...


2

Job.PersonID does not have a unique constraint I think this is what you mean to have But is is still messed up in only one person can have a job If that is the case then just combine Job and Employee CREATE TABLE Employee ( PersonID INT NOT NULL, FirstName CHAR(30) NOT NULL, LastName CHAR(30) NOT NULL, CONSTRAINT Pk_PersonID PRIMARY KEY(...


1

The solution proposed by Ziggy will work, but if you don't want to go that deep by having to create a privileged user just to execute the trigger, there's another solution you can try: Create a validation trigger on the product_stock table that checks if the update is coming from the stock_transaction_log trigger. To do that, we can create a temporary table ...



Top 50 recent answers are included