Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Posts
    123

    Unanswered: constraint on column

    Hmm,

    I'm creating a table that contains information from two other tables. I'd like to have a constraint on some columns so that thay can only contain information that exists in the other two tables. This is what I got:

    create table dbo.KUSE_Bills
    (
    SERVICE_ID int IDENTITY(1,1) NOT NULL primary key,
    BILLDATE datetime NOT NULL,
    CNAME varchar(100) NOT NULL,
    SNAME varchar(100) NOT NULL,
    PRICE money NOT NULL,
    UNIT varchar(50) NOT NULL,
    NUMBER integer NOT NULL,
    BILLSUM money NOT NULL
    )
    GO

    I tried something like this:

    create table dbo.KUSE_Bills
    (
    SERVICE_ID int IDENTITY(1,1) NOT NULL primary key,
    BILLDATE datetime NOT NULL,
    CNAME varchar(100) NOT NULL,
    SNAME varchar(100) NOT NULL,
    PRICE money NOT NULL,
    UNIT varchar(50) NOT NULL,
    NUMBER integer NOT NULL,
    BILLSUM money NOT NULL

    CONSTRAINT chk_cname CHECK (CNAME NOT IN (
    SELECT CNAME FROM KUSE_Customer))
    )
    GO

    But subqueries are not allowed...

    So how can I do it?

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    perhaps the better question is why are we duplicating information across tables. Why not just do a foriegn key to KUSE_Customer?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jul 2003
    Posts
    123
    Hmm.... then I can remove several columns from my table.. and add foreign keys for customer (cname) and service (sname).

    How about this:

    create table dbo.KUSE_Bills
    (
    SERVICE_ID int IDENTITY(1,1) NOT NULL primary key,
    CUSTOMER_ID int NOT NULL references KUSE_Customer(CUSTOMER_ID),
    SERVICE_ID int NOT NULL references KUSE_Service(SERVICE_ID),
    BILLDATE datetime NOT NULL,
    PRICE money NOT NULL,
    NUMBER integer NOT NULL,
    BILLSUM money NOT NULL
    )
    GO

    The I can make a view that summarize the information for the users. Now there are almost nu duplicate information. "Almost" because PRICE is sometimes filled in by the user and sometimes from the SERVICE table.

    Thanks, looks much cleaner.

  4. #4
    Join Date
    Jul 2003
    Posts
    123
    I see I made a small misstake, the primary key should ofcourse be named BILL_ID...

Posting Permissions

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