Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2014
    Posts
    1

    Unanswered: problem with making assertions

    Hi. Im using microsoft sql server and Im learning basics of sql...
    I have a table that contains name of patients and their primary doctors.
    I want to check that a doctor can not be a primary doctor for more than 4 patients
    here is what I have tried:

    CREATE ASSERTION assertion12
    (not exist(
    select DOCTOR.SSN
    from Patient, DOCTOR
    where Patient.primaryDoctor_SSN = DOCTOR.SSN
    group by DOCTOR.SSN
    having count(primaryDoctor_SSN) >= 5
    );


    but I get the error in the first line: "could not find stored procedure" . (actualy a red line comes under "CREATE" and show this message )

    What should I do?! I have check some sites and I saw writing assertions is like what I have done and I dont know why do I get this error.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    SQL Server does not support ASSERTIONS. Oracle does.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    I want to check that a doctor can not be a primary doctor for more than 4 patients
    here is what I have tried:
    T-SQL does not have the ANSI/ISO Standard SQL CREATE ASSERTION, so you have to fake it.

    >> I have a table that contains name of patients and their primary doctors. I want to check that a doctor can not be a primary doctor for more than 4 patients.

    CREATE TABLE Primary_Doctor_Assignments
    (doctor_ssn CHAR(9) NOT NULL
    REFERENCES Doctors (doctor_ssn)
    ON DELETE CASCADE,
    patient_ssn CHAR(9) NOT NULL
    REFERENCES Patients (patient_ssn)
    ON DELETE CASCADE,
    patient_seq INTEGER NOT NULL
    CHECK (patient_seq BETWEEN 1 AND 4),
    PRIMARY KEY (doctor_ssn,patient_ssn,patient_seq)
    );

    You have problems with the column names in your posting. Data elements have on and only one name and it is independent of the location in which it appears. Being a primary is not an attribute of a patient, but a relationship among doctors and patients, etc.

Tags for this Thread

Posting Permissions

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