Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2011
    Posts
    12

    Unanswered: In need of CREATE TABLE help in Distributed Databases

    I have 4 clinics (A,B,C,D).
    Clinic 'A' is the central database, in which it can fetch information from the other databases.
    The CREATE TABLE is below with the CHECK constraints:
    For clinic 'A':
    Code:
    CREATE TABLE Patient
    (patientNumb CHAR(5),
    SS CHAR(9) NOT NULL,
    name VARCHAR2(20) NOT NULL,
    dateOfBirth DATE NOT NULL
    CHECK(dateOfBirth<= to_date(sysdate,'MM/DD/YYYY')),
    address VARCHAR2(15) NOT NULL,
    dateAdmitted DATE NOT NULL
    CHECK(dateAdmitted<= to_date(sysdate,'MM/DD/YYYY')),
    clinic CHAR(1) NOT NULL
    CHECK(clinic='A'),
    CONSTRAINT pk_patientNumb PRIMARY KEY(patientNumb));
    For clinic 'B':
    Code:
    CREATE TABLE Patient
    (patientNumb CHAR(5),
    SS CHAR(9) NOT NULL,
    name VARCHAR2(20) NOT NULL,
    dateOfBirth DATE NOT NULL
    CHECK(dateOfBirth<= to_date(sysdate,'MM/DD/YYYY')),
    address VARCHAR2(15) NOT NULL,
    dateAdmitted DATE NOT NULL
    CHECK(dateAdmitted<= to_date(sysdate,'MM/DD/YYYY')),
    clinic CHAR(1) NOT NULL
    CHECK(clinic='B'),
    CONSTRAINT pk_patientNumb PRIMARY KEY(patientNumb));
    For clinic 'C':
    Code:
    CREATE TABLE Patient
    (patientNumb CHAR(5),
    SS CHAR(9) NOT NULL,
    name VARCHAR2(20) NOT NULL,
    dateOfBirth DATE NOT NULL
    CHECK(dateOfBirth<= to_date(sysdate,'MM/DD/YYYY')),
    address VARCHAR2(15) NOT NULL,
    dateAdmitted DATE NOT NULL
    CHECK(dateAdmitted<= to_date(sysdate,'MM/DD/YYYY')),
    clinic CHAR(1) NOT NULL
    CHECK(clinic='C'),
    CONSTRAINT pk_patientNumb PRIMARY KEY(patientNumb));
    For clinic 'D':
    Code:
    CREATE TABLE Patient
    (patientNumb CHAR(5),
    SS CHAR(9) NOT NULL,
    name VARCHAR2(20) NOT NULL,
    dateOfBirth DATE NOT NULL
    CHECK(dateOfBirth<= to_date(sysdate,'MM/DD/YYYY')),
    address VARCHAR2(15) NOT NULL,
    dateAdmitted DATE NOT NULL
    CHECK(dateAdmitted<= to_date(sysdate,'MM/DD/YYYY')),
    clinic CHAR(1) NOT NULL
    CHECK(clinic='D'),
    CONSTRAINT pk_patientNumb PRIMARY KEY(patientNumb));
    The main question is:
    Do I have to create a new table for the central database 'A' with the following CHECK constraint?:
    CHECK(clinic IN('A','B','C','D')

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Do you know the difference between Oracle database & schema?
    How many of each do you plan on having.

    >Do I have to create a new table for the central database 'A' with the following CHECK constraint?:
    >CHECK(clinic IN('A','B','C','D')
    Have to? NO
    Why do you think any such CHECK is desired or even appropriate?

    Normalize data to Third Normal Form; unlike posted DDL
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2007
    Posts
    623
    Just in addition to anacedent:
    Quote Originally Posted by Plural View Post
    The CREATE TABLE is below with the CHECK constraints:
    For clinic 'A':
    Code:
    CREATE TABLE Patient
    (patientNumb CHAR(5),
    SS CHAR(9) NOT NULL,
    name VARCHAR2(20) NOT NULL,
    dateOfBirth DATE NOT NULL
    CHECK(dateOfBirth<= to_date(sysdate,'MM/DD/YYYY')),
    address VARCHAR2(15) NOT NULL,
    dateAdmitted DATE NOT NULL
    CHECK(dateAdmitted<= to_date(sysdate,'MM/DD/YYYY')),
    clinic CHAR(1) NOT NULL
    CHECK(clinic='A'),
    CONSTRAINT pk_patientNumb PRIMARY KEY(patientNumb));
    This statement is invalid in Oracle - you cannot use nondeterministic function (as SYSDATE) in CHECK constraint. For achieving this condition, you would have to use trigger. Yes, you may "cheat" it with changing the server system date, but the same would apply to the check constraint. Oracle is not capable to re-check it every time SYSDATE changes (which is happening every second).

    Moreover, the use of TO_DATE function on SYSDATE is simply wrong.
    Maybe, before using any function, you should study its behaviour with expected input and output including its data type(s). This information is described in SQL Reference book, which is available with other Oracle documentation books e.g. online on http://tahiti.oracle.com/

    Based on this, answers on these question may lead you to correct expression:
    - what data types are supported in the first parameter of TO_DATE function?
    - what is the return data type of TO_DATE function?
    - what is the data type of SYSDATE function?
    - is is making sense to convert DATE to (the same) DATE? if any manipulation is needed (e.g. truncating time component), what about using proper function it naturally and not as side effect?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •