Foreign key constraint

[[NOPE]FOREVER]

I have a specialization hierarchiy with one parent table and two children. I am in a situation where one column in the child table is a pk, fk, fk and unique.

TICKET_NUMBER = pk, fk1, fk2, u1

This is how I have currently tried to create this table but im not too sure if I have done it right or not.

CREATE TABLE PART_ITEM
(
    TICKET_NUMBER       INT         NOT NULL CONSTRAINT FK1_PART_ITEM_TICKET_NUMBER REFERENCES dbo.TICKET_LINES(TICKET_NUMBER),
    TICKET_LINE_NUMBER  INT         NOT NULL,
    LINE_TYPE           VARCHAR(30) NOT NULL CONSTRAINT CHECK_PART_ITEM CHECK(LINE_TYPE = 'Part')--check to make sure that this is a part item line
                                             FOREIGN KEY REFERENCES dbo.TICKET_LINES(LINE_TYPE),--reference TICKET_LINES table
    PART_ID             INT         NOT NULL CONSTRAINT PART_ITEM_UI1 UNIQUE(PART_ID)-- set unique
                                             FOREIGN KEY REFERENCES dbo.PARTS(PART_ID), --reference PARTS table
    QUANTITY            INT         NOT NULL,
    FOREIGN KEY(TICKET_NUMBER, TICKET_LINE_NUMBER) REFERENCES dbo.TICKET_LINES(TICKET_NUMBER, TICKET_LINE_NUMBER),
    FOREIGN KEY (TICKET_NUMBER) REFERENCES dbo.TICKET_LINES(TICKET_LINE_NUMBER),
    UNIQUE(TICKET_NUMBER),
    PRIMARY KEY(TICKET_NUMBER, TICKET_LINE_NUMBER)
);

Cheers!

[hericles]

That is a lot of constraints for one table and, without knowing your exact intention, I do have to say I'm unsure about it.
Is the part ID really going to be unique across all rows?

And having a line_type column that can only ever equal 'part' seems like a waste of a column.

[[NOPE]FOREVER]

Thankyou, it is kindof hard to explaini with out seeing the logical model here is the link to my dropbox to view it, you will get a better understanding of what i mean. https://www.dropbox.com/s/1jfue1gr3nffale/logical.vsd

cheers

[Cesc_1]

I've not oppended the visio file, but it seems to be a little mess with foreign keys... remember that when you reference a table with a foreig key contraint it must be UNIQUE (or PK) in the referenced table.

I think one of the things you want to check is that a PART_ITEM always refers to o a"Part" line; then you shoud define computed columns on the Ticket_Lines table, so that they are NULL when it is not a "Part" line; those columns must be UNIQUE, so that the FK can be defi nedreferencing them.

[hericles]

[Nope]Forever, you seem to be getting an awful lot of page votes for your posts. I see you got 6 up votes for saying thank you in one thread, and not this one either, (seeing as you got 6 votes for the comment above as well).

Even more interesting, a lot of the votes (if not all) come from two new users from Wellington. Which is where you're from... Are you spamming the system?