Creating a table with three fields in SQL

Tags:
SQL tables
I need to make a table with these fields: prof_name varchar2(20) subject_code varchar2(5) dept varchar2(5) The constraint on this table is that one professor can take, at most, two subjects. This means that the name of the professor appear, at most, two times in the prof_name column. How could this table be constructed in SQL?

Answer Wiki

Thanks. We'll let you know when a new response is added.

AFAIK it is not possible to define a check constraint that makes counts of the table records.

I think you will need to put this validations on the data entering application, and I would suggest this value (2) being stored on the database, so the app doesn’t need to be modified if this policy changes.

Other approach could be to add a new column (subjects_count) which will be updated each time a new row is added (or an update is made). This new column could have a check constraint to only allow values not greater than 2 (<=2), so, the update to this column will fail if it is the third row for a given professor.

Hope this helps.

***********************************

Using this technique you could also add a unique constraint to the prof_name and the subjects_count columns so that data can not be entered in more than once.

***********************************
The CREATE TABLE script in ORACLE would look something like this:

<pre>create table professor_subject (
prof_name varchar2(20),
subject_code varchar2(5),
dept varchar2(5),
subjects_count number(5)
constraint chk_profesorsubject_subjcount check (subjects_count <= 2),
constraint unq_professorsubject_pn_sjc unique (prof_name,subjects_count));</pre>

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following