New answers tagged

3

Using a UUID column as a foreign key is fine, and if that is the only candidate key on the parent record recommended/required. The FK must refer to a value that is unique in the referred table, this usually means the primary key though any column (or combination of columns) defined as unique by way of a constraint or index will do. Some suggest that it ...


3

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 ...


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 ...


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 ...


-1

Just think of of this: The child table owner schema begins to create record in its table and unknowingly prevents the parent table schema user from deleting records from the parent table. Is it something it anticipates and appreciates?


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

There is a mismatch between you and your DBMS regarding special values. Special values are special in your mind, but the DBMS can't tell that a special value is different from an ordinary value. So when you put a special value in, it expects a valid reference to a row in the lookup table. If the reference is invalid, it flags a constraint error. There is ...



Top 50 recent answers are included