CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2008
Hacker inside
Hacker inside

Reputation: 39

SQL Syntax error with foreign keys

now i have table place CREATE TABLE [dbo].[Place] ( [Place_Id] INT IDENTITY (1, 1) NOT NULL, [Name] VARCHAR (50) NOT NULL, [Building_Date] DATE NULL, [Longitude] VARCHAR (50) NULL, [Latitude] VARCHAR (50) NULL, [Location] VARCHAR (50) NOT NULL, PRIMARY KEY CLUSTERED ([Place_Id] ASC) ); , and table Citeria CREATE TABLE [dbo].[Criteria] ( [Criteria] VARCHAR (50) NOT NULL, [Place_Id] INT NOT NULL, PRIMARY KEY CLUSTERED ([Criteria], [Place_Id]), CONSTRAINT [FK_Criteria_ToTable] FOREIGN KEY (Place_Id) REFERENCES Place(Place_Id) );and The referenced table '[dbo].[Criteria]' contains no primary or candidate keys that match the referencing column list in the foreign key. If the referenced column is a computed column, it should be persisted. .

Upvotes: 0

Views: 1048

Answers (2)

Ajay2707
Ajay2707

Reputation: 5798

It wrong db design, never use varchar or character type column in reference key or primary-key(try to avoid as much as possible).

For you solution, create a column "useid" with int datatype and give pk to it. and update the following table

CREATE TABLE [dbo].[Likes] (
    [User_id] VARCHAR (50) Identity (1,1),
    [User_Name] VARCHAR (50) NOT NULL,
    [Place_Id]  INT          NOT NULL,
    CONSTRAINT [PK_Likes] PRIMARY KEY CLUSTERED ([User_id] ASC, [Place_Id] ASC),
    CONSTRAINT [FK_Likes_ToTable] FOREIGN KEY ([User_Name]) REFERENCES Normal ([User_Name]),
    CONSTRAINT [FK_Likes_ToTable_1] FOREIGN KEY ([Place_Id]) REFERENCES [dbo].[Place] ([Place_Id]),

);

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269493

The error is correct. Subqueries are not allowed in check constraints.

But, you already have a foreign key reference between user_name and likes(user_name), so this condition is already in place. The only thing is would really be checking is that user_name is not NULL, but that is already true by the definition of the column.

Now, there are other issues. Your foreign keys should be to primary keys or unique keys in other tables. I think this is your intention:

CREATE TABLE [dbo].Normal_Upload
(
    [User_Name] VARCHAR(50) NOT NULL , 
    [Place_Id] INT NOT NULL, 
    [Image] IMAGE NOT NULL, 
    CONSTRAINT [FK_Normal_Upload] FOREIGN KEY (User_Name) REFERENCES Member(User_Name), 
    CONSTRAINT [FK_Normal_Upload_1] FOREIGN KEY (Place_Id) REFERENCES Place(Place_Id), 
    CONSTRAINT [FK_Normal_Upload_2] FOREIGN KEY (User_Name, Place_Id) REFERENCES Likes(User_Name, Place_Id)
);

As a note on naming. I think the primary keys of tables should include the table name. So, consider Member_Name rather than User_Name for the Member table.

Upvotes: 2

Related Questions