Consider the following relational database where the primary keys are as indicat
ID: 3623491 • Letter: C
Question
Consider the following relational database where the primary keys are as indicatedperson(person name, street, city) // records about all people (primary key is person name)
works(person name, company name, annual salary) // records about employed people(primary key is person name)
company(company name, founded)(primary key is company name)
company location(company name, city) // companies and cities where they have offices(primary keys are company name and city)
managed by(person name, manager name)(primary key is person name)
Consider this schema . Write a check condition
to ensure that an employee cannot be the manager of his/her manager. For example, if John isGeorge's manager, George cannot be John's manager.
ANS- I am using postgreSQL 9.0
I have written this query following way but i am getting error please help me to fix the error.
CREATE Table managed_by (
person_name varchar(30),
manager_name varchar(30),
CHECK (manager_name != (SELECT person_name FROM managed_by WHERE manager_name = person_name)),
CONSTRAINT managed_by_pk PRIMARY KEY(person_name),
FOREIGN KEY(person_name) REFERENCES person(person_name),
FOREIGN KEY(manager_name) REFERENCES person (person_name)
);
ERROR: cannot use subquery in check constraint
Explanation / Answer
Dear... CREATE Table managed_by ( person_name varchar(30), manager_name varchar(30), CHECK (person_name = manager_name AND manager_name != person_name) CONSTRAINT managed_by_pk PRIMARY KEY(person_name), FOREIGN KEY(person_name) REFERENCES person(person_name) FOREIGN KEY(manager_name) REFERENCES person (person_name) ); Note: A check constraint allows you to specify a condition on each row in a table. A check constraint only checks the values of a single row. So it can never check a business rules that potentially needs to look at all other rows in order to verify it. So, it might get away with a trigger, but that would still have some issues in a multi-user environment.