CODEWITHSUNDEEP
sql-server
user8435866
user8435866

Reputation:

Sql condition with two foriegn key columns

    I'm new to sql and
    have a table dbo.Student




**column**                             **datatype**
     Student Id Primary Key            int not null
    ClassId Foriegn Key                int not null
    BaseId        Foriegn Key          int not null

ClassId can have multiple base Id but not duplicates I'm looking to write a condition If any ClassId have duplicate rows with same BaseId's

    Example for good data
    a b  c
    1 2  1
    2 2  2
    Example for bad data
    a b  c
    1 2  1
    2 2  1


    Thanks

Upvotes: 0

Views: 55

Answers (2)

ASP
ASP

Reputation: 666

You can use below query to check if you have any duplicate rows in the table

select ClassId,baseid, COUNT(*) NumberOfDuplicate
from student
group by ClassId,baseid
having count(*) >1

Upvotes: 1

user7396598
user7396598

Reputation: 1289

There are "unique" constraints in SQL server. You can add one to that column and it will prevent the entry of a row in which that column value is not unique. Unique constraints are allowed on columns that are not keys.

This SO answer has both the gui solution and script solution:

stackoverflow.com/questions/5181877/

Gist of linked answer:

ALTER TABLE TableName ADD CONSTRAINT ConstraintName UNIQUE(ColumnName1, ColumnName2)

This will put a unique constraint on the combination of Column1 and Column2.

In SSMS object Explorer if you expand the table and look under "Keys" to see your constraint.

GUI steps:

Open SQL Server Management Studio.
Expand the Tables folder of the database where you want to create the 
constraint.
Right-click the table where you want to add the constraint and click Design.
In the Table Designer menu, click Indexes/Keys.
In the Indexes/Keys dialog box, click Add.
Choose Unique Key in the Type drop-down list.

Upvotes: 1

Related Questions