Search code examples
sqloracle-databaseconstraintscreate-table

Oracle SQL create tables with some constraints

first time using Oracle DB.

I'm starting with a very basic Person table: ["ID", "Age", "Workclass", "fnlwgt", "Education", "Education-Num", "Martial Status", "Occupation", "Relationship", "Race", "Sex", "Capital Gain", "Capital Loss", "Hours per week", "Country", "Target"]

And a Relationship table: ["PersonID", "RelativeID", "Relation"]

  • The workclass of a person can be only one of the given values: Private, Self-emp-not-inc and few other values
  • A person age must be larger than his child.
  • A parent must be at least 12 years old
  • A man cannot earn more than his wife

my first thought was to check those things in the code level of my client but I guess there are better ways to achieve this with pure sql.

I want to create the tables with those constraints but I really know nothing about oracle SQL

Solution

  • to meet your criteria .. I'd try the following:

    • The workclass of a person can be only one of the given values: Private, Self-emp-not-inc and few other values

    Setup another table, named "workclass_type" with the following data:

    ID WorkClass_Name 1 Private 2 Self-emp 3 .... and whatever other values you need.

    Change your "workclass" column on Person table to "workclass_ID" and create a FK on this column in person table pointing to the ID col in workclass_type.

    This is called a referential constraint (or foreign key - same thing). It tells Oracle to limit choices to what's in that other table. It's good for space savings (among other things), since you don't store the full string name on every row .. just a small numeric ID.

    You can / should populate the ID column via sequence ... but I'll let you discover that ;)

    • A person age must be larger than his child.

    Not easily done via constraints ... you could do it via trigger, however, I would NOT advise it .. Triggers have their own set of issues, and often lead to bigger issues. I'd recommend a stored procedure (as Renato suggested in comments).

    Create an INS procedure .. and force everyone to come in via that routine. So in other words, don't expose the table to INS/UPD ... but expose the Procedure instead.

    (and when I say procedure, I mean a procedure within a Package .. easier .. again .. I'll let you discover that now that you have the terminology) :)

    • A parent must be at least 12 years old

    again, part of your procedure above.

    • A man cannot earn more than his wife

    again, part of your procedure above.