SAP ASE (Sybase) - Foreign key list in the database

USE [DATABASENAME]
GO

-- Quick script
select * from sysobjects where type = 'RI'


-- Detailed script
select 
  tab.name    "table name",
  fko.name    "Foreign key name",
  par.name    "Referenced table name",
--  fk1.name  "FK column name",
--  pk1.name  "Ref column name",
  *
from  sysobjects      tab
JOIN  sysconstraints  con on tab.id        = con.tableid
JOIN  sysobjects      fko on con.constrid  = fko.id
JOIN  sysreferences   ref on con.constrid  = ref.constrid
JOIN  sysobjects      par on par.id        = ref.reftabid
--  ---- 1. Column
--  left join syscolumns  fk1 on ref.fokey1 = fk1.colid and ref.tableid = fk1.id
--  left joinsyscolumns   pk1 on ref.refkey1 = pk1.colid and ref.reftabid = pk1.id
-- etc. to more columns
where tab.type = 'U'
and   fko.type = 'RI'
--and   tab.name = 'TABLENAME'

SAP ASE (Sybase) - Create FOREIGN KEY on exists table

IF NOT EXISTS (select * from sysobjects where name = 'FK_NAME' and type = 'RI')
  ALTER TABLE [dbo].[TABLENAME] ADD CONSTRAINT FK_NAME FOREIGN KEY ([COLUMNNAME])
  REFERENCES [dbo].[REF_TABLENAME] ([REF_COLUMNNAME])
GO

Get all constraints of a specific type

select *

from information_schema.table_constraints

where CONSTRAINT_TYPE = 'UNIQUE' 

                -- 'CHECK' , 'UNIQUE' , 'PRIMARY KEY' , 'FOREIGN KEY'

Get Constraints details include columns

select t.TABLE_SCHEMA, t.TABLE_NAME,

       t.CONSTRAINT_SCHEMA, t.CONSTRAINT_NAME, t.CONSTRAINT_TYPE,

       c.COLUMN_NAME

from information_schema.table_constraints t

join information_schema.constraint_column_usage c 

         on t.Constraint_Name = c.Constraint_Name

--where t.CONSTRAINT_NAME = 'UC_Something'

--where t.CONSTRAINT_TYPE = 'Unique'

--where t.TABLE_NAME = 'TableName'

...

Get Default constraint detailes include columns

SELECT df.name as DFconstraintName, a.name as ColumnName

FROM   sys.all_columns a

JOIN   sys.tables t     ON a.object_id = t.object_id

JOIN   sys.schemas s    ON t.schema_id = s.schema_id

JOIN   sys.default_constraints df ON a.default_object_id = df.object_id

WHERE  s.name = 'SchemaName'

AND    t.name = 'TableName'

AND    a.name = 'ColumnName'

Check constraint on a column value for each ID in the table

Our goal: to check in the DB level that for each ID (or other column) will be other column with a specific value not more than one time.

Wow - long statement… Let's do it with an example :)

CREATE TABLE [dbo].[SpecialCheckConst]

(      SomeID int,

       SomeStatusID int, -- we will check vaalues of this column

       SomeOtherColumn nvarchar(50) -- or any other columns

)

We want to check that for each SomeID there is no more than one record of SomeStatusID 1.

INSERT INTO dbo.SpecialCheckConst

       (      SomeID, SomeStatusID, SomeOtherColumn)

       VALUES

       (      2, 3, 'aaa'), (5, 1, 'sss')

When we will try to insert new record with SomeStatusID 1 for SomeID 2 - we should success.

When we will try to insert new record with SomeStatusID 1 for SomeID 5 - we should fail (because there is a record for this SomeID with status 1).

When we will try to insert new record with new SomeID - of course that SomeStatusID 1 is OK.

Solution: 

CHECK CONSTRAINT that calls a function that count the number of SomeStatusID 1 for the SomeID:

CREATE FUNCTION [dbo].[fn_SpecialCheckConst_SomeStatusIDOne]

(

       @SomeID int

)

RETURNS INT

AS

BEGIN

       DECLARE @SomeStatusIDOne int

       SELECT @SomeStatusIDOne = COUNT(*)

       FROM dbo.SpecialCheckConst

       WHERE SomeID = @SomeID

       AND SomeStatusID = 1

       RETURN @SomeStatusIDOne

END

GO

---------------------------------------------------------------------------------------------

ALTER TABLE [dbo].[SpecialCheckConst]  WITH CHECK ADD  CONSTRAINT [CHK_dbo_SpecialCheckConst_SomeStatusIDOne]

CHECK  (NOT ([dbo].[fn_SpecialCheckConst_SomeStatusIDOne](@SomeID) > 1 AND SomeStatusID = 1))

GO

ALTER TABLE [dbo].[SpecialCheckConst] CHECK CONSTRAINT [CHK_dbo_SpecialCheckConst_SomeStatusIDOne]

GO

---------------------------------------------------------------------------------------------

And ….. It’s work!

You can drop the example temp table:

--DROP TABLE dbo.SpecialCheckConst

--go