How to Create a Foreign Key in SQL Server

How do I create a foreign key in SQL Server?

create table question_bank
(
    question_id uniqueidentifier primary key,
    question_exam_id uniqueidentifier not null,
    question_text varchar(1024) not null,
    question_point_value decimal,
    constraint fk_questionbank_exams foreign key (question_exam_id) references exams (exam_id)
);

SQL Server : create a foreign key with a condition

You can use a check constraint for this.
(The code is untested some syntax errors will be in there)

CREATE TABLE Member
(
   P_Id int NOT NULL,
   LastName varchar(255) NOT NULL,
   FirstName varchar(255),
   Address varchar(255),
   City varchar(255),
   InternalContactId
   CONSTRAINT chk_Person CHECK (isInternalUser(internalContactId) > 0)
)

ALTER TABLE Member
ADD FOREIGN KEY (InternalContacId)
REFERENCES Persons(P_Id)

Then just create a function isInternalUser that returns 1 if user in ok to be an internal contact

CREATE FUNCTION isInternalUser ( @userId int(10) )
  RETURNS int
  AS
  BEGIN
      DECLARE @tmp int
        SELECT @tmp = count(*)
          FROM users
        WHERE userId = @UserId and <check to see if user is internal>
      RETURN(@CtrPrice)
  END
GO 

Adding named foreign key constraints in a SQL Create statement

In SQL Server, you can use the constraint keyword to define foreign keys inline and name them at the same time.

Here's the updated script:

CREATE TABLE  galleries_gallery (
    id              INT NOT NULL PRIMARY KEY IDENTITY,
    title           NVARCHAR(50) UNIQUE NOT NULL,
    description     VARCHAR(256),
    templateID      INT NOT NULL 
        CONSTRAINT FK_galerry_template 
        REFERENCES galleries_templates(id),
    jsAltImgID      INT NOT NULL 
        CONSTRAINT FK_gallery_jsAltImg
        REFERENCES libraryImage(id)
    jsAltText       NVARCHAR(500),
    dateCreated     SMALLDATETIME NOT NULL,
    dateUpdated     SMALLDATETIME NOT NULL,
    lastUpdatedBy   INT,
    deleted         BIT NOT NULL DEFAULT 0
);

I just made a test and apparently the same thing also works in PostgreSQL: http://www.sqlfiddle.com/#!12/2ae29

Creating a foreign key against a composite key in MS SQL Server

The foreign key on the product_price_history table should only include product_id. Your target is to ensure that any entry product_price_history already has "parent" entry in products. That has nothing to do with start_date.

The way I see this situation, in theory, fully normalized version of the tables would have to have current_price as unique value in products table. And the product_price_history is simply a log table.

It's not necessary to do it this way, with a physical field, but thinking from this perspective helps to see where your tables model is slightly de-normalized.

Also, if you make product_price_history table anything but simple log table, how do you ensure that new start_date is newer than previous end_date? You can't even express that as a primary key. What if you edit start_date later? I would even think to create different compaund key for product_price_history table. Perhaps product_id+insert_date or only auto-increment id, while still keeping foreign key relationship to the products.product_id.

Transact SQL: how to create double sided foreign key constraints?

You have to do the first constraint with a separate ALTER TABLE, because the reference table has not yet been defined. SQL has no concept of a "forward reference" in this case.

CREATE TABLE A (
 idA INT PRIMARY KEY,
 idB INT
);

CREATE TABLE B (
 idB INT PRIMARY KEY,
 idA INT FOREIGN KEY REFERENCES A(idA)

);

ALTER TABLE A
    ADD CONSTRAINT FK_A_B FOREIGN KEY (idB) REFERENCES B(idB);

Some database designers have the habit of doing all foreign key constraints after the table definitions so they don't have to worry about the order the table definitions.

They usually arrive at this pattern after adding a new foreign key somewhere and watching a script fail because of the ordering of the table declarations.

Here is a db<>fiddle.

---

Related Topics