Will not specifying a foreign key constraint get me in trouble?

I have the following T-SQL in VS2013:

CREATE TABLE [dbo].[Jugo] 
(
    [JugoID]  INT           IDENTITY (1, 1) NOT NULL,
    [Jugo]    NVARCHAR (50) NOT NULL,
    [ColorID] INT           NOT NULL,
    [IngreID] INT           NOT NULL,

    PRIMARY KEY CLUSTERED ([JugoID] ASC),
    FOREIGN KEY (ColorID) REFERENCES Color (ColorID) 
        ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY (IngreID) REFERENCES Ingrediente (IngreID) 
        ON UPDATE CASCADE ON DELETE CASCADE 
);

And I get the following message before updating the database:

Highlights None

User actions Create Foreign Key: unnamed constraint on [dbo].[Jugo] (Foreign Key) Foreign Key: unnamed constraint on [dbo].[Jugo] (Foreign Key)

Supporting actions None

Can I proceed with these unnamed constraints? What would I need the constraints for?

For reference this is what I'm trying to do:

3 tables:

Jugo , Color , Ingrediente : so to create a jugo (juice) you specify name, color and ingredients, color and ingredients are on their own tables, hence the foreign keys I want to define.

Thanks.

---

As long as the ColorID and IngreID are both primary key in their own tables, you won't have any trouble.

But here's the catch, you didn't constrain your primary keys. It is a good practice to use constraint for every PRIMARY KEY you are using, in that way, when you foreign key it to another table. It can read that it is from another table. I don't know about SQL in VS2013 But here's my sample for SQL Server:

CREATE TABLE JUGO 
(
     JUGOID numeric identity (1,1) //For auto increment
        CONSTRAINT PK_JUGOID PRIMARY KEY (JUGOID),
     JUGO nvarchar(50) not null,
     .
     .
     .
)

The PK_JUGOID in CONSTRAINT PK_JUGOID PRIMARY KEY (JUGOID), is user-defined, but it is a good practice to just copy the format of the primary key so you won't be confused when using foreign keys. :)

---

Foreign keys are for data consistency. You create the foreign keys on columns, so as not to allow invalid entries.

FOREIGN KEY (ColorID) REFERENCES Color (ColorID) 
    ON UPDATE CASCADE ON DELETE CASCADE,

This constraint makes sure that your jugo can only have a color that exists in the color table. Moreover you have two cascade commands:

ON UPDATE CASCADE means that if color red is ID 5, but you want to change it to 500, the change will be performed automatically for all jugo records. However, an ID should never change, so this is a clause seldom used.

ON DELETE CASCADE means that if you delete red, then you delete all red jugo records with it. I don't know if this is an appropriate case for you. It is used for instance on a users table. Remove a user, so you remove its complete data. But remove a color?

At last you should give your constraints names, so it's easier for you to deal with them.

CONSTRAINT pk_jugo PRIMARY KEY CLUSTERED ([JugoID] ASC),
CONSTRAINT fk_jugo_color FOREIGN KEY (ColorID) REFERENCES Color (ColorID),
CONSTRAINT fk_jugo_ingre FOREIGN KEY (IngreID) REFERENCES Ingrediente (IngreID)

链接地址: http://www.djcxy.com/p/65892.html

上一篇: IMDB是否提供API？

下一篇: 不会指定一个外键约束让我陷入麻烦？