locked
CREATE TABLE Syntax RRS feed

  • Question

  • please I need a second opinion to verify if this syntax is correct or if theres a better way of doing it

    create table management.payments
    (paymentid int identity not null,
    patientid int not null constraint PK_patientid references patient.patientdetails(patientid),
    getdate datetime,
    paymentdate datetime check (paymentdate >= getdate )


    • Edited by Kalman Toth Wednesday, February 20, 2013 6:38 AM incomplete
    Tuesday, February 12, 2013 9:36 PM

Answers

  • A few tweaks (and I would also include a primary key and rename the foreign key constraint). I highlighted the changes I made

    create table management.payments
    (paymentid int identity not null constraint PKPayments,
     patientid int not null constraint FKPaymentsReferencesPatientDetails references patient.patientdetails(patientid),
     getdate datetime, --probably ought to rename getdate as it is a system function, but it create with that name
     paymentdate datetime,  --column check constraints can only reference the one column, table constraints (declared on their own), any column
      constraint payments_paymentDateRange check (paymentdate >= getdate ) -- name not required, but nice to name check constraints too.


    Louis

    Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.

    • Proposed as answer by Naomi NEditor Wednesday, February 13, 2013 12:48 AM
    • Marked as answer by Kalman Toth Wednesday, February 20, 2013 6:24 AM
    Tuesday, February 12, 2013 11:56 PM

All replies

  • A few tweaks (and I would also include a primary key and rename the foreign key constraint). I highlighted the changes I made

    create table management.payments
    (paymentid int identity not null constraint PKPayments,
     patientid int not null constraint FKPaymentsReferencesPatientDetails references patient.patientdetails(patientid),
     getdate datetime, --probably ought to rename getdate as it is a system function, but it create with that name
     paymentdate datetime,  --column check constraints can only reference the one column, table constraints (declared on their own), any column
      constraint payments_paymentDateRange check (paymentdate >= getdate ) -- name not required, but nice to name check constraints too.


    Louis

    Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.

    • Proposed as answer by Naomi NEditor Wednesday, February 13, 2013 12:48 AM
    • Marked as answer by Kalman Toth Wednesday, February 20, 2013 6:24 AM
    Tuesday, February 12, 2013 11:56 PM
  • In addition  you need GETDATE() with parentheses
    parentheses
    parentheses

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Wednesday, February 13, 2013 6:43 AM
  • Here is my take:

    create table management.payments (
    	PaymentID int identity(1,1) constraint PK_payment PRIMARY KEY,
    	PatientID int not null constraint FK_patientid references patient.patientdetails(patientid),
    	PaymentDate datetime constraint chkPayDate CHECK (paymentdate >= getdate() ),
    	UNIQUE(PatientID, PaymentDate),
    	Amount money not null constraint chkAmount CHECK (Amount > 0.0),
    	ModifiedDate datetime default getdate());


    Kalman Toth Database & OLAP Architect
    Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

    Wednesday, February 20, 2013 6:37 AM